Обсуждение: timezone difference in timestamp?
Short version: I'm trying to turn a unix timestamp into a psql timestamp, but there is a 5 hour difference. Is this because of timezones ? Can I just subtract 5 hours to get the right value ? Detailed version: I start with a unix timestamp: $startofday =1162789200; Which has this date: Array ( [seconds] => 0 [minutes] => 0 [hours] => 0 [mday] => 6 [wday] => 1 [mon] => 11 [year] => 2006 [yday] => 309 [weekday] => Monday [month] => November [0] => 1162789200 ) Then I make a pgsql timestamp like this: "select timestamp '1970-01-01' + interval '$startofday seconds' as timestamp" And the result is: timestamp 2006-11-06 05:00:00 timestamp It seems like it should be 2006-11-06 00:00:00 This seems to work but can I always get the correct value by subtracting 5 hours like this? "select timestamp '1970-01-01' + interval '$startofday seconds'-interval '5 hours' as timestamp"
On Mon, Nov 06, 2006 at 03:30:35PM +0000, meltedown wrote: > Short version: I'm trying to turn a unix timestamp into a psql > timestamp, but there is a 5 hour difference. Is this because of > timezones ? Can I just subtract 5 hours to get the right value ? Not sure what you're using, but by my calculations postgresql is correct. $ perl -e 'print scalar(gmtime(1162789200)),"\n"' Mon Nov 6 05:00:00 2006 Make sure you understand whether the dates your comparing are in the same timezone. Maybe you want 'timestamp with time zone'. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
meltedown <asdf@fake.com> writes: > Short version: I'm trying to turn a unix timestamp into a psql > timestamp, but there is a 5 hour difference. Is this because of > timezones ? Can I just subtract 5 hours to get the right value ? > "select timestamp '1970-01-01' + interval '$startofday seconds' as > timestamp" If it really is a Unix timestamp --- ie, referenced to midnight GMT 1970-01-01 --- then you need to start with midnight GMT not midnight local time as the basis. So, select timestamp with time zone '1970-01-01 00:00 GMT' + interval ... or better yet select timestamp with time zone 'epoch' + interval ... regards, tom lane
Tom Lane wrote: > meltedown <asdf@fake.com> writes: >> Short version: I'm trying to turn a unix timestamp into a psql >> timestamp, but there is a 5 hour difference. Is this because of >> timezones ? Can I just subtract 5 hours to get the right value ? > >> "select timestamp '1970-01-01' + interval '$startofday seconds' as >> timestamp" > > If it really is a Unix timestamp --- ie, referenced to midnight GMT > 1970-01-01 --- then you need to start with midnight GMT not midnight > local time as the basis. So, > > select timestamp with time zone '1970-01-01 00:00 GMT' + interval ... > > or better yet > > select timestamp with time zone 'epoch' + interval ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > Thanks, that answered my question. I thought as much, but thanks for that fine explanation.