CODEWITHSUNDEEP
mysqldatetime
ripper234
ripper234

Reputation: 230058

Using Now() as a default for MySql DateTime type

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?

Upvotes: 0

Views: 641

Answers (4)

ripper234
ripper234

Reputation: 230058

Anax was close, but missed the default value.

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

Upvotes: 4

Anthony
Anthony

Reputation: 37065

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

Upvotes: 2

Brian Fisher
Brian Fisher

Reputation: 23989

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 ;

Upvotes: 1

Anax
Anax

Reputation: 9372

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

Upvotes: 8

Related Questions