Timestamp

[mreaves53]

How do I create a time stamp column that uses the CURRENT_TIMESTAMP on an update as well as in insert?

[peterlaursen]

the answer is that you won't have to do so. With the more recent MySQL versions MySQL itself automatically creates the first timestamp column of a table as

Code:

NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

However you will have to wait a few days forSQLyog to fully support this, se http://www.webyog.com/forums/index.php?sho…view=getnewpost . 5.1 beta1should be out by the end of this week.

The problem is that SQLyog (like most grid-based editor) as of now simple reads CURRENT_TIMESTAMP as the literal string 'CURRENT_TIMESTAMP' and inserts this string, when you are saving from the grid. The workaround to avoid this is to work from the RESULT-pane: start issuing a SELCT column1, column2 etc (and omit the timestamp column). Now when inserting and updating data from result-pane the timestamp-column is not overwritten by SQLyog, and 'automatic rule' of the server takes effect.

There is a good article here: http://www.databasejournal.com/features/my…cle.php/3577131 . But the basic behaviour with timestamps in thsi respect is the same with 4.1 and 5.0. MySQL docs: http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

[peterlaursen]

Two small notes more:

1) There is one difference of MySQL 4.1 and 5.x

When SQLyog tries to insert literal string 'CURRENT_TIMESTAMP' MySQL 4.1 accepts that. With MySQL 5.0 (in most sql_modes) the string is substitued with '0000-00-00 00:00:00'.

2) It does not seem possible for the SQLyog ALTER TABLE GUI to generate the ' on update ….' right now. You must write SQL like

Code:

alter table `test`.`tbl` change `ts` `ts` timestamp  DEFAULT CURRENT_TIMESTAMP NOT NULL on update CURRENT_TIMESTAMP

to make 'on update ..' work with the test.tbl.ts -column.

[mreaves53]

Thanks for the info and heads up. I wrote the script like you suggested. Worked fine.

Thanks,

[Author]

Posts