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/
Show quoted text
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.