Hi all, Does anyone know what is going on here:
//Query: select UNIX_TIMESTAMP(TIMESTAMP('2003-01-01 00:00:00')) as first, UNIX_TIMESTAMP(TIMESTAMP('2003-10-05 00:00:00')) as second, UNIX_TIMESTAMP(TIMESTAMP('2004-01-01 00:00:00')) as third; +------------+------------+------------+ | first | second | third | +------------+------------+------------+ | 1041400800 | 1065330000 | 1072936800 | +------------+------------+------------+ // Converting timestamps to UTC using linux date command (could also use http://www.4webhelp.net/us/timestamp.php) $ date -u -d @1072936800 Thu Jan 1 06:00:00 UTC 2004 $ date -u -d @1041400800 Wed Jan 1 06:00:00 UTC 2003 $ date -u -d @1064984400 Wed Oct 1 05:00:00 UTC 2003 MySQL seems to treat the local time as being UTC -6 hours in the first two cases but as UTC -5 in other cases. The system local time appears to be UTC-5 (EST): // Attempting to determine MySQL's timezone offset: select UNIX_TIMESTAMP(UTC_TIMESTAMP()) - UNIX_TIMESTAMP(now()) as offset: +--------+ | offset | +--------+ | 18000 | +--------+ which is consistent with the last result, but not the first two. I have not yet tested more dates throughout the year to see when the change occurs, and if there is a pattern, but I though I'd ask first to see if anyone else has either encountered this before, or knows what is going on? I would like to be able to store some UTC datetimes in a system that uses localtime, and then extract them as UTC timestamps again, which is why I'm trying to figure out the proper offset. On this particular system I also do not have the ability to change the default timezone (e.g. to UTC/GMT), so I'm stuck with using local dates. Any suggestions? Any help would be greatly appreciated :) Thanks! -Keith