MySQL CURRENT_TIMESTAMP to create and update

Question

I want to define a table that will have 2 TIMESTAMP fields, something 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 the error:

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

The point is to save the desired behavior of ts_create and ts_update in the table schema.

Answer 1

This is a mySQL limitation; you cannot have two TIMESTAMP columns with default values ​​that reference CURRENT_TIMESTAMP. The only way to do this is to use the DATETIME type for ts_create, which unfortunately cannot have a default value of NOW (). You can fire your own trigger for this to happen.

Answer 2

Guess this is an old post, but in fact I assume that mysql supports 2 TIMESTAMP in their latest releases of mysql 5.6.25, which is what they are currently using.

Answer 3

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

Answer 4

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

Answer 5

I think this is possible using the method below

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

Answer 6

I think you might want ts_create as datetime (so rename → dt_create) and only ts_update as timestamp? This ensures that it remains unchanged after installation.

I understand that datetime is for manual values, and timestamp is a bit “special” in that MySQL will support it for you. In this case, datetime is a good choice for ts_create.

Answer 7

I would say that you do not need to have DEFAULT CURRENT_TIMESTAMP on your ts_update: if it is empty, then it does not update, so your last update is ts_create.

Answer 8

This is a tiny limitation of Mysql in the old version, actually after version 5.6, and then several timestamps work ...