unexpected behavior in combining timestamps with times zone and intervals

From: Charles Seaton <cseaton(at)stccmop(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: unexpected behavior in combining timestamps with times zone and intervals
Date: 2007-11-03 11:26:13
Message-ID: 472C5AD5.5030108@ccalmr.ogi.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Niklas Johansson 2007-11-03 13:26:52 Re: unexpected behavior in combining timestamps with times zone and intervals
Previous Message Shane Ambler 2007-11-03 10:10:15 Re: Populating large DB from Perl script