Using Now() as a default for MySql DateTime type

Question

How can I setup a MySql schema with a DateTime column such that its value will always be the time of the last insert/update to this row?

Answer 1

You could do it with triggers:

DELIMITER $$
CREATE TRIGGER `before_update` BEFORE UPDATE ON `table_name` FOR EACH ROW BEGIN
 SET NEW.update_date = NOW();
END;
$$

CREATE TRIGGER `before_insert` BEFORE INSERT ON `table_name` FOR EACH ROW BEGIN
 SET NEW.update_date = NOW();
END;
$$

DELIMITER ;

Answer 2

You can the TIMESTAMP instead and set the ON UPDATE CURRENT_TIMESTAMP NOT NULL, for example:

CREATE TABLE example (
lastUpdate TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL 
);

Answer 3

Like Anax said, using TIMESTAMP instead of DATETIME will give you a column that uses the current time instead of waiting for a provided date/time.

You have a lot of options with TIMESTAMP, the two that are most important are:

• Having it use the current time ( NOW() ) when the row is created,
• Having it modify the column to the current time when the row is modified.

If you don't set any options, like so:

 CREATE TABLE stuff (ts TIMESTAMP);

It will put in the current time on creation and change that column on modification.

So if you want a column that only shows when it was created, you would use:

 CREATE TABLE stuff (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Since you have not specified an ON UPDATE clause, it will not do anything if the row is updated.

If you are insane, like me, you'll want both, and go with:

 CREATE TABLE stuff (create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                     mod_date TIMESTAMP
                    );

and since I haven't written this out yet, that last one is equivalent to writing out:

 CREATE TABLE stuff (create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                     mod_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP
                    );

Answer 4

Anax was close, but missed the default value.

CREATE TABLE example (
lastUpdate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
)