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();

Hess

Answer 1

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

Mena

latest versions of mysql doesn't support multiple time-stamp, So we can have one timestamp and multiple triggers. – Ouzel

Answer 2

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

Jordon

Can I add another task with this >> " SET NEW.last_visited= NOW();" ? I did but occurred errors.Thanks . @Jordon – Polystyrene

@MuhammadAshikuzzaman You can separate them using a comma. So SET NEW.created_date = NOW(), NEW.last_visited = NOW(); – Ise

Answer 3

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

Wrong

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... – Outline

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! – Wrong

Answer 4

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.

1. http://www.mysqltutorial.org/sql-triggers.aspx
2. http://www.tutorialspoint.com/plsql/plsql_triggers.htm

These two toturial will help to implement the trigger.

Ligon