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.

I believe the error is clear and what you seek is the alternative way to use do the same thing like current_timestamp — GusDeCooL, Jun 16 '12 at 15:40
Use recent version of MySQL(5.6.5+), that should work for you! — Amol Fasale, Jul 13 '18 at 05:30
as per current documentation this syntax should be working:https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html — Channa, Apr 03 '21 at 16:39

avinash v p · Accepted Answer · 2016-03-15T08:09:11.037

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.

edited Mar 15 '16 at 08:09

answered Feb 16 '16 at 07:59

avinash v p

531
2
8
15

score 7 · Answer 2 · answered Mar 05 '15 at 10:52

7

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

answered Mar 05 '15 at 10:52

iKing

667
10
15

'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. – imVJ Aug 30 '16 at 09:48
This is actually painful also in MySQL, you shouldn't use zeros to initialise a timestamp or date/time column. – David Vartanian Oct 18 '17 at 08:15
Whether zeroes are allowed as a valid timestamp depends on the current SQL mode. Docs: https://dev.mysql.com/doc/refman/5.5/en/sql-mode.html#sqlmode_no_zero_date – alexg Jul 13 '18 at 14:20

score 5 · Answer 3 · answered Apr 28 '15 at 15:51

5

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

answered Apr 28 '15 at 15:51

Himanshu Shekhar

314
3
7

The question was from 2011, MySQL 5 was brand new then. – kuba Feb 21 '20 at 08:34

score 1 · Answer 4 · answered Sep 30 '14 at 00:44

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

answered Sep 30 '14 at 00:44

clemquinones

251
2
6

score 0 · Answer 5 · answered Oct 12 '17 at 14:19

0

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

answered Oct 12 '17 at 14:19

Sonpal singh Sengar

237
2
2

score 0 · Answer 6 · answered Jan 24 '18 at 11:40

0

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

answered Jan 24 '18 at 11:40

AJavaLover

1

score 0 · Answer 7 · answered Feb 04 '11 at 10:40

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.

score 0 · Answer 8 · answered Feb 04 '11 at 10:45

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.

answered Feb 04 '11 at 10:45

Nanne

64,065
16
119
163

you can't have a ON UPDATE TIMESTAMP if you have another field with TIMESTAMP in DEFAULT – Jeger May 24 '12 at 12:57
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". – Nanne Feb 21 '13 at 10:14

MySQL CURRENT_TIMESTAMP on create and on update

8 Answers8

Linked

Related