Hi Keith, I'm not sure, but this might be DST that's in your way. Have you looked into that?
Have a nice day, - Martijn On Mon, Apr 20, 2009 at 18:34, Keith Hughitt <keith.hugh...@gmail.com> wrote: > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org