The following bug has been logged online:
Bug reference: 5325
Logged by: Eric Vollnogel
Email address: edvollnogel@dstsystems.com
PostgreSQL version: 8.4.2
Operating system: Windows XP
Description: Timestamp w/ timezone + interval not functioning
correctly
Details:
I have encountered a problem in which adding a timestamp with timezone to a
duration is resulting in an incorrect timestamp with timezone depending on
if the duration causes the sum to cross a daylight savings boundary.
For example, the following query shows a calculation without crossing a
daylight savings boundary:
SELECT cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME
ZONE 'US/Eastern' AS timestamp1, cast('2010-03-02 00:00:00' AS timestamp
without time zone) AT TIME ZONE 'US/Eastern' AS timestamp2, cast('2010-03-02
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' -
cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' as interval, (cast('2010-03-02 00:00:00' AS timestamp without
time zone) AT TIME ZONE 'US/Eastern' - cast('2010-03-01 00:00:00' AS
timestamp without time zone) AT TIME ZONE 'US/Eastern') + (cast('2010-03-01
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern') as
shouldbetimestamp1
Results in the following output:
timestamp1, timestamp2, interval, shouldbetimestamp1
"2010-02-28 23:00:00-06";"2010-03-01 23:00:00-06";"1 day";"2010-03-01
23:00:00-06"
This output is correct. The output shows timestamp1, timestamp2,
timestamp2-timestamp1, and (timestamp2-timestamp1)+timestamp1. timestamp2
should always be equal to (timestamp2-timestamp1)+timestamp1.
For the next example, we cross the daylight savings time boundary:
SELECT cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME
ZONE 'US/Eastern' AS timestamp1, cast('2010-04-15 00:00:00' AS timestamp
without time zone) AT TIME ZONE 'US/Eastern' AS timestamp2, cast('2010-04-15
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' -
cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE
'US/Eastern' as interval, (cast('2010-04-15 00:00:00' AS timestamp without
time zone) AT TIME ZONE 'US/Eastern' - cast('2010-03-01 00:00:00' AS
timestamp without time zone) AT TIME ZONE 'US/Eastern') + (cast('2010-03-01
00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern') as
shouldbetimestamp1
This results in the following output:
timestamp1, timestamp2, interval, shouldbetimestamp1
"2010-02-28 23:00:00-06";"2010-04-14 23:00:00-05";"44 days
23:00:00";"2010-04-14 22:00:00-05"
The output in this example is incorrect. Because 1 hour is lost when
daylight savings takes effect, the interval is correct: 45 days - 1 hour.
(44 days 23:00:00). However the last computation,
(timestamp2-timestamp1)+timestamp1 should equal timestamp2, but clearly does
not.
2010-04-14 23:00:00-05 does not equal 2010-04-14 22:00:00-05.
Thank you for your assistance in this matter,
ERIC