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?

heisenberg · Accepted Answer · 2010-07-12 19:11:11Z

4

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

answered Jul 12, 2010 at 19:11

heisenberg

9,7691 gold badge32 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user213154 Over a year ago

Thank you. I was looking in the wrong section. For anyone interested, it's at the top of 10.3 in both the 5.0 and 5.1 manuals.

Collectives™ on Stack Overflow

Using zero value in MySQL columns of type TIMESTAMP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related