Re: unexpected behavior in combining timestamps with times zone and intervals

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.)
>