Home

Geeklog: What I Learnt Today / Adam

Differences between MySQL Timestamp and Datetime columns

> Whilst at first there seems not be a great deal of difference between DATETIME and TIMESTAMP columns. In practice there is a big difference that you could come a cropper by. In the MySQL manual it says:
"MySQL converts |TIMESTAMP| values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as |DATETIME|.) ... ... If you store a |TIMESTAMP| value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored."
That last bit is very important should be in very big letters probably. So for example on my desktop if I store the current time with the computer set to the Australia Sydney Timezone and then set the time zone on the computer back to GMT I get a time out of the TIMESTAMP column that doesn't match what I put in. This is either really great because the values adjusted to the current time zone auto magically or really bad because your not getting back what your put in to your database. DATETIME keeps the same time regardless of the servers change in time zone.
As a demonstration if I create this table

CREATE TABLE `test` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `ts` timestamp NULL DEFAULT NULL,
   `dt` datetime DEFAULT NULL,
   `desc` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
and enter

2013-03-03 03:03:03
In both the 'ts' and 'dt' of the columns whilst my computer is set to 'Australian Eastern Daylight Time'.
When I reset my time zone to 'GMT' I get this back

ts = 2013-03-02 16:03:03
dt = 2013-03-03 03:03:03

This most probably would never be an issue unless your MySQL server changes its time zone unnoticed (perhaps after a restart) at some point and then you might be glad you had chosen DATETIME.

/ Adam