Re: BUG #3260: Subtracting intervals

"Dhugael McLean" <box(at)yourtechonline(dot)com> writes:
> select '1 day'::interval - '55 minutes'::interval;

> ?column?
> -----------------
> 1 day -00:55:00

> If the interval periods are both minutes (hours - hours, days - days, etc),
> this works fine. Days - minutes seems to fail. This should output 23:05:00.

No, this result is correct IMHO. Days and minutes are not interconvertible,
because there are not always 24 hours in a day. As an example using
EST5EDT zone (current US DST law):

regression=# select '2007-03-11'::timestamptz;
timestamptz
------------------------
2007-03-11 00:00:00-05
(1 row)

regression=# select '2007-03-11'::timestamptz + '1 day'::interval;
?column?
------------------------
2007-03-12 00:00:00-04
(1 row)

regression=# select ('2007-03-11'::timestamptz + '1 day'::interval) - '55 minutes'::interval;
?column?
------------------------
2007-03-11 23:05:00-04
(1 row)

regression=# select '2007-03-11'::timestamptz + ('1 day'::interval - '55 minutes'::interval);
?column?
------------------------
2007-03-11 23:05:00-04
(1 row)

regression=# select '2007-03-11'::timestamptz + '23:05:00'::interval;
?column?
------------------------
2007-03-12 00:05:00-04
(1 row)

Postgres gets the fourth case right, but would fail if we adopted
your approach, as shown by the fifth case.

regards, tom lane