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