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

User · Accepted Answer

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

User · Answer

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

User · Answer

You are using older MySql version  Update your myqsl to 5 6 5  it will work

User · Answer

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

User · Answer

This is the tiny limitation of Mysql in older version   actually after version 5 6 and later multiple timestamps works

User · Answer

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

User · Answer

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

User · Answer

you can try this     ts create TIMESTAMP DEFAULT CURRENT TIMESTAMP      ts update TIMESTAMP DEFAULT NULL ON UPDATE  CURRENT TIMESTAMP

[mysql] MySQL CURRENT_TIMESTAMP on create and on update

Examples related to mysql

Examples related to timestamp

Examples related to mysql-error-1293