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;
Compare the difference.
This means you need to be VERY careful when changing the time zone of your server!
If you want to see this example on YouTube, check out
this video from a presentation I did at the 2010 O'Reilly MySQL Conference and Expo, start watching at 5:00 (5 minutes in) and watch until 7:30 (7 minutes, 30 seconds)
