"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