MySQL set current date in a DATETIME field on insert

Question

I have a 'created_date' DATETIME field which I would like to be populated with the current date upon insert. What syntax should I use for the trigger for this? This is what I have so far but it is incorrect.

CREATE TRIGGER set_created_date
BEFORE INSERT ON product
FOR EACH ROW BEGIN
SET NEW.created_date = now();

score 42 · Answer 1 · answered Oct 17 '12 at 14:01

42

DELIMITER ;;
CREATE TRIGGER `my_table_bi` BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
    SET NEW.created_date = NOW();
END;;
DELIMITER ;

answered Oct 17 '12 at 14:01

pevik

4,523
3
33
44

1

Can I add another task with this >> " SET NEW.last_visited= NOW();" ? I did but occurred errors.Thanks . @pevik – Muhammad Ashikuzzaman May 26 '14 at 13:38
@MuhammadAshikuzzaman You can separate them using a comma. So `SET NEW.created_date = NOW(), NEW.last_visited = NOW();` – Zei Oct 23 '20 at 02:59

score 20 · Accepted Answer · edited May 16 '14 at 00:57

20

Your best bet is to change that column to a timestamp. MySQL will automatically use the first timestamp in a row as a 'last modified' value and update it for you. This is configurable if you just want to save creation time.

See doc http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

edited May 16 '14 at 00:57

Mark Ott

62
2
10

answered Jul 16 '09 at 17:05

dunedain289

2,348
18
21

2

latest versions of mysql doesn't support multiple time-stamp, So we can have one timestamp and multiple triggers. – Ahmed Syed Feb 28 '16 at 07:36

score 19 · Answer 3 · answered Aug 28 '15 at 15:22

19

Since MySQL 5.6.X you can do this:

ALTER TABLE `schema`.`users` 
CHANGE COLUMN `created` `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;

That way your column will be updated with the current timestamp when a new row is inserted, or updated.

If you're using MySQL Workbench, you can just put CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP in the DEFAULT value field, like so:

http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html

answered Aug 28 '15 at 15:22

Coreus

5,360
3
35
50

This works, thank you. But it's not really answering the question because including the `ON UPDATE` clause here means it gets updated each time any field gets updated, which is obviously not what you want for a "Created" field, but for a "LastModified" field. But apart from that... – mike rodent Mar 25 '22 at 12:10
That depends entirely on how the system is incorporated. If the table is flushed reguarly, this would make sense. Also, talk about ressurrecting an old one! – Coreus Mar 26 '22 at 02:06

score 3 · Answer 4 · answered Dec 11 '14 at 16:05

Using Now() is not a good idea. It only save the current time and date. It will not update the the current date and time, when you update your data. If you want to add the time once, The default value =Now() is best option. If you want to use timestamp. and want to update the this value, each time that row is updated. Then, trigger is best option to use.

These two toturial will help to implement the trigger.

MySQL set current date in a DATETIME field on insert

4 Answers4

Linked