How to create 2 auto-set timestamp columns in a MySQL table?

Question

I want a MySQL table of mine to contain 2 timestamp columns, both set automatically without the client side help: one to be initialized once on insert:

`added` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

and another initialized the same on insert and updated on every update:

`updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

But this doesn't work this way:

[Err] 1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Is there a known workaround?

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

There is not just a workaround, there is a solution: Upgrade to MySQL 5.6.5 or higher and this is supported.

See: MySQL 5.6.6 TIMESTAMP columns and DEFAULT values

{edit} Since upgrading is not an option, you can make the first column a normal timestamp column and create a trigger that sets one timestamp when you insert the record. Then you can create the other colum with the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, so it gets a timestamp on insertion and on update.

CREATE TRIGGER task_creation_timestamp BEFORE INSERT ON tasks 
FOR EACH ROW
SET NEW.created = NOW();

I've stolen this trigger from this answer.

edited May 23 '17 at 12:21

Community

1
1

answered Sep 22 '14 at 18:12

GolezTrol

114,394
18
182
210

Thanks, but unfortunately there is no chance I convince my shared hosting provider to update anything until they come to this idea themselves. I upvote your answer anyway. – Ivan Sep 22 '14 at 18:16
You can use a trigger as well. I've just added that suggestion to my answer, but for an example, you might check [this question](http://stackoverflow.com/questions/2906978/mysql-how-to-create-trigger-for-setting-creation-date-for-new-rows). – GolezTrol Sep 22 '14 at 18:18

score 1 · Answer 2 · answered Sep 22 '14 at 18:21

1

There is no "solution" as the error suggest, you can ONLY HAVE ONE TIMESTAMP per table (On previous versions of 5.6.6 as GolezTrol Suggested)

To workaround this i suggest you make of the "timestamps" a datetime and set the default to NOW() or CURRENT_TIMESTAMP() or any other synonym for NOW()

answered Sep 22 '14 at 18:21

Azteca

549
6
19

The same `TIMESTAMP` data type seems to work just fine so I can see no reason to use `DATETIME` here (yes, I like `DATETIME` better normally but as far as we have use `TIMESTAMP` already it seems preferable to keep with it just to avoid complications). – Ivan Sep 22 '14 at 18:36

How to create 2 auto-set timestamp columns in a MySQL table?

2 Answers2