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;

 

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)

 

Comments

+1

Tue, 05/24/2011 - 13:42

It's also worth considering what declaring an index on a timestamp column to be unique asserts: the users will make no time zone changes that could cause duplicate values to be present.  MySQL currently returns only one of the matching rows if a time zone change causes duplicate values to be present and the unique index is used.  James Day, MySQL Principal Support Engineer, Oracle

Mon, 05/23/2011 - 13:48

Reply

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.
Website by Digital Loom