From: | Charles Seaton <cseaton(at)stccmop(dot)org> |
---|---|
To: | spot(at)tele2(dot)se |
Cc: | pgsql general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: unexpected behavior in combining timestamps with times zone and intervals |
Date: | 2007-11-04 05:18:14 |
Message-ID: | 472D5616.1010406@ccalmr.ogi.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Niklas,
Thanks for your response. I am wondering whether it is postgres 8.2.5
that resolves the problem or your time zone setting.
I am running postgres 8.0.3. My server time zone is set to US/Pacific.
Setting my server time zone to one that does not have daylight saving
time causes the problem to vanish:
begin;
set local time zone -8;
select ('2006-12-31 UTC'::timestamptz + '307 days 02:45:30'::interval) AT
TIME ZONE 'UTC';
timezone
---------------------
2007-11-03 02:45:30
set local time zone 'US/Pacific';
select ('2006-12-31 UTC'::timestamptz + '307 days 02:45:30'::interval) AT
TIME ZONE 'UTC';
timezone
---------------------
2007-11-03 01:45:30
rollback;
thanks,
Charles Seaton
Niklas Johansson wrote:
> On 3 nov 2007, at 12.26, Charles Seaton wrote:
>> select ('12/31/2006 UTC'::timestamptz + '307 days 02:45:30'::interval)
>> However, this gives an incorrect result (off by 1 hour)
>> "2007-11-02 18:45:30-07"
>
> Have you checked your servers TimeZone setting? Also, which Postgres
> version are you running? I get the following from 8.2.5, which seems
> to be correct:
>
> test=# select ('2006-12-31 UTC'::timestamptz + '307 days
> 02:45:30'::interval) AT TIME ZONE 'MST';
> timezone
> ---------------------
> 2007-11-02 19:45:30
>
> (I have a different DateStyle setting, so I had to change the input
> format. I also added the 'AT TIME ZONE' statement, since my server's
> in another time zone.)
>
From | Date | Subject | |
---|---|---|---|
Next Message | Myshkin LeVine | 2007-11-04 05:27:19 | Re: Problem starting the server with Mac OSX |
Previous Message | Tom Lane | 2007-11-04 01:50:49 | Re: Copy the database.. |