DATETIME vs. TIMESTAMP

Shlomi blogged over the weekend about DATETIME vs. TIMESTAMP, and hit all the major differences, except one extremely important one that very few people know about (in my opinion).

Coincidentally, last week's OurSQL podcast was about time, and included the most important difference between TIMESTAMP vs. DATETIME:

TIMESTAMP is stored, transparently to you and me, in UTC.  DATETIME is not.  The best way to explain this is with an illustration -- go to a MySQL instance on a machine whose system time zone you can change, and run the following example:

CREATE TABLE time_test (dt datetime, ts timestamp);

INSERT INTO time_test (dt, ts) VALUES (NOW(), NOW());

SELECT dt,ts from time_test;

Now, shut down MySQL, change the system time zone, start up MySQL again, and run:

SELECT dt,ts from time_test;