Re: [SQL] Interval subtracting

Mark Dilger <pgsql(at)markdilger(dot)com> writes:
> Tom Lane wrote:
>> I guess I would expect a good result to satisfy one of these three
>> cases:
>> * month > 0 and 0 <= day < 30
>> * month < 0 and -30 < day <= 0
>> * month = 0 and -30 < day < 30
>> If you believe that then "1 month -95 days" should justify to
>> "-2 months -5 days".

> How would you expect justify_hours to behave? I extrapolate from your rules
> above that:

> * month > 0 and 0 <= day < 30 and 0 <= hours < 24
> * month < 0 and -30 < day <= 0 and -24 < hours <= 0
> * month = 0 and -30 < day <= 0 and -24 < hours <= 0
> * month = 0 and 0 <= day < 30 and 0 <= hours < 24

Hmmm ... I think it would be better if the two functions were
independent, if possible. Your spec above implies that justify_hours
implicitly does justify_days as well, which seems a bit restrictive.

Furthermore, justify_hours should only assume that 1 day == 24 hours,
which while broken by DST is still a lot solider assumption than
justify_days' 1 month == 30 days. I can well believe that a lot of
people only want to make the first assumption.

So I'm inclined to think that justify_hours is responsible for reducing
the seconds part to less-than-24-hours and pushing any overflow into the
days part (but not touching months), while justify_days is responsible
for reducing the days part to less-than-30-days and pushing any overflow
into the months part (but not touching seconds). If you want both you
apply both functions, probably in that order. (I wonder if there are
any cases where applying justify_days before justify_hours would be
useful. Offhand I can't see one ...)

regards, tom lane