Help with timestamp/phpMyAdmin

jbachris

Using phpMyAdmin to setup my news manager.

I imported the content from a previous database on a different server. When I imported the info the TIMESTAMP field changed. It was:

YYYYMMDD

Now, for some reason the only format I'm allowed to use for timestamp is:

0000-00-00 00:00:00

Which I don't want to do. I try to edit the field in phpMyAdmin, and set it to TIMESTAMP with a value of 8, but it still defaults it to 0000-00-00 00:00:00. I'm using phpMyAdmin 2.8.1, the old server used a 2.6 version.

tsinka

If the version of the old MySQL server was <4.1 and the version of the new MySQL server is >=4.1 then the problem you described is the expected result. Since version 4.1 the type timestamp has the same format as the datetime field. So if you try to import a pre 4.1 timestamp field into a 4.1 or later timestamp field the resulting value will most likely be 0000-00-00 00:00:00 because MySQL versions 4.1 and later don't accept an old timestamp format value as valid value.

I don't think that this has something to do with phpMyAdmin itself.

Thomas

jbachris

tsinka wrote:
If the version of the old MySQL server was <4.1 and the version of the new MySQL server is >=4.1 then the problem you described is the expected result. Since version 4.1 the type timestamp has the same format as the datetime field. So if you try to import a pre 4.1 timestamp field into a 4.1 or later timestamp field the resulting value will most likely be 0000-00-00 00:00:00 because MySQL versions 4.1 and later don't accept an old timestamp format value as valid value.

I don't think that this has something to do with phpMyAdmin itself.

Thomas

Thanks. How (if possible) can I setup a field like the old timestamp format?

tsinka

What to do depends a little bit on how your application uses the timestamp field.

How many tables with timestamp fields do you have and how do you use them in your application (e.g. do you just fetch the values or do you use the UNIX_TIMESTAMP function to fetch the fields as unix timestamp) ?

How complicated will it be to change the php scripts so that they work with both timestamp fields and datetime fields ?

Do you need the functionality that MySQL automatically sets the value of the first timestamp field in a table to the current date/time ?

Thomas

jbachris

tsinka wrote:
What to do depends a little bit on how your application uses the timestamp field.

How many tables with timestamp fields do you have and how do you use them in your application (e.g. do you just fetch the values or do you use the UNIX_TIMESTAMP function to fetch the fields as unix timestamp) ?

How complicated will it be to change the php scripts so that they work with both timestamp fields and datetime fields ?

Do you need the functionality that MySQL automatically sets the value of the first timestamp field in a table to the current date/time ?

Thomas

It would be EXTREMELY complicated/time consuming to change my php scripts. I do not need the last funcationality you mentioned.

tsinka

How do you use the timestamp values in your scripts ?

Thomas