MySQL CURRENT_TIMESTAMP on create and on update

Question

I want to define table which will have 2 TIMESTAMP fields, someting like this:

CREATE TABLE `msgs` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `msg` VARCHAR(256),
    `ts_create` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `ts_update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

How to do this avoiding error:

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Point is to keep desired behavior of ts_create and ts_update in table schema.

Solution 1

Guess this is a old post but actually i guess mysql supports 2 TIMESTAMP in its recent editions mysql 5.6.25 thats what im using as of now.

Solution 2

i think it is possible by using below technique

`ts_create` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`ts_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Solution 3

You are using older MySql version. Update your myqsl to 5.6.5+ it will work.

Solution 4

You cannot have two TIMESTAMP column with the same default value of CURRENT_TIMESTAMP on your table. Please refer to this link: http://www.mysqltutorial.org/mysql-timestamp.aspx

View more solutions

Comments

[Jeger]

you can't have a ON UPDATE TIMESTAMP if you have another field with TIMESTAMP in DEFAULT

[Nanne]

I know. But the question was not "why does this happen", but "how can I avoid it". This answers that question I believe better then "change the datatypes and make a trigger".

[imVJ]

'0000-00-00 00:00:00' is not valid timestamp, though mysql will allow, many other databases do not allow '0000-00-00 00:00:00' as default to timestamp datatype, so at certain time, if you are migrating away from mysql, this would be painful.

[David Vartanian]

This is actually painful also in MySQL, you shouldn't use zeros to initialise a timestamp or date/time column.

[alexg]

Whether zeroes are allowed as a valid timestamp depends on the current SQL mode. Docs: dev.mysql.com/doc/refman/5.5/en/…

[kuba]

The question was from 2011, MySQL 5 was brand new then.