DATETIME DEFAULT NOW() finally available.

Having been rather desired for a while now, this feature is finally available as of MySQL Server version 5.6.5. It started out as the innocuous bug #27645 back in 2007, not really commanding much attention from anyone. But since, the page has seen around a hundred posts from users. This is a lot of posts for a bug page.

When I got to work on this, I started out looking at how functions in the default clause worked for the one supported case, CURRENT_TIMESTAMP (a.k.a. NOW() in MySQL) for TIMESTAMP columns. It turned out to be a little more complex than it had to, the TIMESTAMP type has a lot of special rules attached to it that no other types have, not even DATETIME.

One thing that struck me as a little odd was that you can only have one column with a function in the default or on update clause. So I had to take the decision how to deal with that restriction as we introduced DATETIME DEFAULT CURRENT_TIMESTAMP. Should we forbid having a TIMESTAMP DEFAULT CURRENT TIMESTAMP if there was a DATETIME DEFAULT CURRENT_TIMESTAMP? No matter how I looked at it, there would have to be a bunch of new convoluted special rules added that I couldn't motivate to myself. Moreover, a lot of users seemed to request having one DATETIME column with "default now" and another one "on update now", so they could track both creation and last update time to a row. The request was not lost on me: I decided to lift the one-column-with-NOW() restriction altogether. It made documentation simpler and avoided having rules applying only TIMESTAMP spill over on DATETIME. As expected, there was no technical reason for forbidding it, and the code involved needed an overhaul anyways.

The rules

I will not attempt to describe the various TIMESTAMP rules here, the manual does a great job at it already. Instead I will present the new set of rules for how DATETIME DEFAULT / ON UPDATE CURRENT_TIMESTAMP works.

1. Trying to store a null value in a DATETIME NOT NULL column will always yield an error.
2. A DATETIME column accepting nulls has NULL as default value unless specified.
3. There are no implicit defaults for DATETIME columns except the one above.
4. A DATETIME DEFAULT CURRENT_TIMESTAMP column will store CURRENT_TIMESTAMP if and only if an insert statement does not list the column or store a full row.
5. A DATETIME ON UPDATE CURRENT_TIMESTAMP column will store CURRENT_TIMESTAMP if the row is updated and the new row is different from the previous row.

These rules sure look simple enough, don't they? Well, of course there are some more ins and outs to it if you look close enough. Hopefully you should not have to memorize all of these scenarios but it would be unfair of me not to at least mention them.

In rule #4, "an insert statement" includes, of course INSERT, LOAD and REPLACE statements. LOAD statements will store "0000-00-00 00:00:00" (a.k.a. the 'zero date') in a DATETIME column if the file contains a null at the column's position. Under strict sql_mode, the entire statement will fail. TIMESTAMP columns still store CURRENT_TIMESTAMP in this case, regardless of default.

In rule #5, the statements that can update a row are UPDATE and INSERT ... ON DUPLICATE KEY UPDATE ... when there is a duplicate primary key.

Lifting the one-column-with-function-default restriction also has some side-effects when you add or move columns.

• As before, the first column in a table that is declared simply "TIMESTAMP" will implicitly be promoted to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. If you add another column that is "TIMESTAMP" before it, they will both be as that long line I just wrote. Previously, you would get an error in this case.
• Oh, yeah, error number 1293, a.k.a. ER_TOO_MUCH_AUTO_TIMESTAMP_COLS [sic], can't happen anymore. There's no limit to how many columns with CURRENT_TIMESTAMP you can have. Knock yourself out.

When is NOW()?

In MySQL, CURRENT_TIMESTAMP is defined as the query start time, the time when the query arrives at the server. This means that the value is constant during the execution of the query. So don't be surprised if you update a million rows of type DATETIME(6) ON UPDATE NOW(6) - microsecond precision - and they all get the same update time.