77

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.

Improve this question
3
  • 1
    I believe the error is clear and what you seek is the alternative way to use do the same thing like current_timestamp Commented Jun 16, 2012 at 15:40
  • 1
    Use recent version of MySQL(5.6.5+), that should work for you! Commented Jul 13, 2018 at 5:30
  • as per current documentation this syntax should be working:dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html Commented Apr 3, 2021 at 16:39

8 Answers 8

Reset to default
20

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

9

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
Improve this answer

3 Comments

'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.
This is actually painful also in MySQL, you shouldn't use zeros to initialise a timestamp or date/time column.
Whether zeroes are allowed as a valid timestamp depends on the current SQL mode. Docs: dev.mysql.com/doc/refman/5.5/en/…
5

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

Improve this answer

1 Comment

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

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

Improve this answer

Comments

0

I think you maybe want ts_create as datetime (so rename -> dt_create) and only ts_update as timestamp? This will ensure it remains unchanging once set.

My understanding is that datetime is for manually-controlled values, and timestamp's a bit "special" in that MySQL will maintain it for you. In this case, datetime is therefore a good choice for ts_create.

Improve this answer

Comments

0

I would say you don't need to have the DEFAULT CURRENT_TIMESTAMP on your ts_update: if it is empty, then it is not updated, so your 'last update' is the ts_create.

Improve this answer

2 Comments

you can't have a ON UPDATE TIMESTAMP if you have another field with TIMESTAMP in DEFAULT
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".
0

This is the tiny limitation of Mysql in older version , actually after version 5.6 and later multiple timestamps works...

Improve this answer

Comments

0

you can try this ts_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ts_update TIMESTAMP DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.