Postgres Pro
Downloads
Home   >   mailing lists

Thread: timezone difference in timestamp?

timezone difference in timestamp?

From
meltedown
Date:
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"

Re: timezone difference in timestamp?

From
Martijn van Oosterhout
Date:
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.
Attachment

Re: timezone difference in timestamp?

From
Tom Lane
Date:
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

Re: timezone difference in timestamp?

From
meltedown
Date:
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.