BUG #5325: Timestamp w/ timezone + interval not functioning correctly - Mailing list pgsql-bugs

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