CODEWITHSUNDEEP
mysqltimestampmysql-error-1293
kuba
kuba

Reputation: 3870

MySQL CURRENT_TIMESTAMP on create and on update

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.

Upvotes: 77

Views: 184504

Answers (8)

AJavaLover
AJavaLover

Reputation: 1

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

Upvotes: 0

Sonpal singh Sengar
Sonpal singh Sengar

Reputation: 247

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

Upvotes: 0

avinash v p
avinash v p

Reputation: 531

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.

Upvotes: 20

Himanshu Shekhar
Himanshu Shekhar

Reputation: 314

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

Upvotes: 5

iKing
iKing

Reputation: 697

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

Upvotes: 9

clemquinones
clemquinones

Reputation: 261

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

Upvotes: 1

Nanne
Nanne

Reputation: 64399

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.

Upvotes: 0

Brian
Brian

Reputation: 6450

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.

Upvotes: 0

Related Questions