Using zero value in MySQL columns of type TIMESTAMP

Question

I'm a little confused over the documentation of type TIMESTAMP in MySQL and whether the zero value is safe to use. The manual says:

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. ...

which implies that '0000-00-00 00:00:00' is out of range and thus not valid. But, as far as I can see, the manual does notactually say this. Nor does it explicitly say that '0000-00-00 00:00:00' is a valid value.

Practice shows that '0000-00-00 00:00:00' works and the manual does say that:

CREATE TABLE t (ts TIMESTAMP DEFAULT 0);

is valid MySQL SQL. In practice it leads to insertion of '0000-00-00 00:00:00' defaults.

(All of this sort of aligns with the curious fact that '1970-01-01 00:00:00' is out of range, which, IIRC, is not the case for a uint32 unix time. Perhaps MySQL uses binary zero as a magic number?)

So is it safe to assign semantics to zero TIMESTAMPs in the application? Or is this an undocumented feature that might go away?

Answer 1

The date and time types for representing temporal values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each temporal type has a range of legal values, as well as a “zero” value that may be used when you specify an illegal value that MySQL cannot represent.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html