Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 568
kuba Avatar asked Feb 04 '11 10:02

kuba


People also ask

What is on update CURRENT_TIMESTAMP in MySQL?

With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp but does not have the current timestamp for its default value. The default in this case is type dependent.

How do I add a TIMESTAMP to a column in MySQL?

Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.

What is the difference between now () and CURRENT_TIMESTAMP?

NOW() returns a constant time that indicates the time at which the statement began to execute. NOW() returns the time at which the function or triggering statement began to execute, but SYSDATE() returns the exact time at which it executes. And CURRENT_TIMESTAMP , CURRENT_TIMESTAMP() are synonyms for NOW() .


Video Answer


4 Answers

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.

like image 198
avinash v p Avatar answered Oct 18 '22 20:10

avinash v p


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
like image 7
iKing Avatar answered Oct 18 '22 20:10

iKing


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

like image 5
Himanshu Shekhar Avatar answered Oct 18 '22 19:10

Himanshu Shekhar


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

like image 1
clemquinones Avatar answered Oct 18 '22 19:10

clemquinones