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

Recently, in working with some data that was expressed as day of year +
time, I discovered an unexpected behavior in postgres's time handling. I
am wondering whether this is a bug or expected behavior that I simply
don't understand.

Given a day of year 307 and a time 04:45:30 UTC in 2007, an obvious way
to convert this to a postgres timestamptz seemed to be the following
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"

The correct result is achieved by working in timestamps without
timezones, and then forcing the timezone at the end
('12/31/2006'::timestamp + '307 days 02:45:30' ::interval || 'UTC'
)::timestamptz
"2007-11-02 19:45:30-07"

It appears that the inclusion of the time zone in the timestamptz causes
the interval to be interpreted in a way that loses 1 hour  (presumably
at the daylight saving transition in spring), even though the timezone
UTC does not have a daylight saving rule and should always be in
standard time.

Does anyone have any insight into why this happens, and whether there is
some circumstance under which this would be desirable behavior?

thanks,

Charles Seaton
Research Associate
OHSU/CMOP

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




Sincerely,

Niklas Johansson

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

Charles Seaton <cseaton@stccmop.org> writes:
> Thanks for your response. I am wondering whether it is postgres 8.2.5
> that resolves the problem or your time zone setting.

This behavior changed in PG 8.1.  Per the release notes:

* Add a separate day field to type interval so a one day interval can be
  distinguished from a 24 hour interval (Michael Glaesemann)

Days that contain a daylight saving time adjustment are not 24 hours
long, but typically 23 or 25 hours. This change creates a conceptual
distinction between intervals of "so many days" and intervals of "so
many hours". Adding 1 day to a timestamp now gives the same local time
on the next day even if a daylight saving time adjustment occurs
between, whereas adding 24 hours will give a different local time when
this happens. For example, under US DST rules:

        '2005-04-03 00:00:00-05' + '1 day' = '2005-04-04 00:00:00-04'
        '2005-04-03 00:00:00-05' + '24 hours' = '2005-04-04 01:00:00-04'

            regards, tom lane