Timestamp Problem

[energylevel]

I'm having a problem with timestamp fields displaying as 00000000000000 when I insert records into a MySQL DB

MySQL version is 3.23 and SQLYog Enterprise version is 5.02 …. if some can suggest what the issue might be I'd be appreciative ….

[peterlaursen]

MySQL 3 does not support 'default CURRENT_TIMESTAMP on update CURRENT TIMESTAMP' with the table definition.

This SQL seems to work however:

Code:

Alter table `tttt`.`tt` ,change `ts` `ts` timestamp (14) DEFAULT 'now()' NOT NULL;

.. although 'show create table' does not return it

then the create_time will be inserted for each new row.

If you want to update with CURRENT_TIMESTAMP when updating a row with SQLyog 5.1 just write CURRENT_TIMESTAMP. 5.02 does not support this!

http://webyog.com/faq/8_99_en.html

@Ritesh:

I request 'smart support' for 'now()' as it is much faster to type than 'CURRENT_TIMESTAMP'!

[Ritesh]

peterlaursen wrote on May 10 2006, 01:26 PM:

http://webyog.com/faq/8_99_en.html

@Ritesh:

I request 'smart support' for 'now()' as it is much faster to type than 'CURRENT_TIMESTAMP'!

Do you mean in autocomplete?

[peterlaursen]

Quote:

Do you mean in autocomplete?

Nope – it is only you that has his head filled with 'auto-complete' 😛

I request 'smart keyword handling' with the literal string 'now()' – just like 'NULL' , 'CURRENT_TIMESTAMP', 'CURRENT_USER' etc.

Writing 'now()' (without quotes) in a TIMESTAMP cell would then send 'now()' (also without quotes) and the server will insert CURRENT_TIMESTAMP. Or put it another way: just as 'localtimestamp' is a synonym for 'CURRENT_TIMESTAMP', 'now()' is too. But 'now()' is the easiest to type.

[energylevel]

timestamp (14) DEFAULT 'now()' NOT NULL

This doesn't seem to be working for me…..

peterlaursen wrote on May 10 2006, 03:59 PM:

Nope – it is only you that has his head filled with 'auto-complete' 😛

I request 'smart keyword handling' with the literal string 'now()' – just like 'NULL' , 'CURRENT_TIMESTAMP', 'CURRENT_USER' etc.

Writing 'now()' (without quotes) in a TIMESTAMP cell would then send 'now()' (also without quotes) and the server will insert CURRENT_TIMESTAMP. Or put it another way: just as 'localtimestamp' is a synonym for 'CURRENT_TIMESTAMP', 'now()' is too. But 'now()' is the easiest to type.

[peterlaursen]

1)

Could you post the 'create statement for the table'?

I would like to see if there is a Priamry Key and if the column is NULL or NOT NULL.

2) Were there data in the table when the TIMESTAMP column was crated?

Now .. if the TIMESTAMP column has been added after data were entered in other columns it will be given values 00000000000000. Actually I have a request pending on that at bugs.mysql.com: http://bugs.mysql.com/bug.php?id=17392

SQLyog 5.1.x checks for the 'on update CURRENT_TIMESTAMP' in the table definition when saving (and skips the column in the INSERT statement if so) – but this clause is not available with MySQL 3.23!

To update an existing cell having 00000000000000 with SQLyog you can do either

1)write the time there yourself in YYYYMMDDHHMMSS -format

2)write 'CURRENT_TIMESTAMP' (without quotes). Now SQLyog omits the column in the INSERT statement and the server will update the field.

If you depend on a better TIMESTAMP handling you should not use MySQL 3.x !! 4.1 or higher!

[energylevel]

Thanks for all your help, it's not such a big deal as the timestamp field populates properly when records are added via web browser/php script which how it will be done most of the time… so I can cope with that …

[Author]

Posts