Thread: BUG #5325: Timestamp w/ timezone + interval not functioning correctly

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

"Eric Vollnogel" <edvollnogel@dstsystems.com> writes:
> The output in this example is incorrect.

Well, that's debatable.  As you say, the result of the subtraction is

     interval
------------------
 44 days 23:00:00
(1 row)

If we add 44 days to timestamp1, we get

select cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' + interval '44 days';
                 
        ?column?
------------------------
 2010-04-14 00:00:00-04
(1 row)

and if we then add another 23 hours to that, we get

select cast('2010-03-01 00:00:00' AS timestamp without time zone) AT TIME ZONE 'US/Eastern' + interval '44 days' +
interval'23 hours'; 
        ?column?
------------------------
 2010-04-14 23:00:00-04
(1 row)

both of which are reasonable answers.

There has been some discussion of changing timestamp subtraction so that
it doesn't reduce the interval to days, but just produces '1079 hours'
in this example.  If it did that then you'd get the result you were
expecting.  Unfortunately, it would also break a whole lot of other
cases.  So far the decision has been to leave it alone.

In the meantime, if you would like that behavior you can get it using
arithmetic on the epoch equivalents, ie

    (extract(epoch from timestamp1) - extract(epoch from timestamp2))
    * interval '1 second'

            regards, tom lane