From: Mark Dilger <pgsql@markdilger.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] Interval subtracting
Date: Wed, 01 Mar 2006 13:04:02 -0800
Message-ID: <44060C42.50905@markdilger.com> (raw)
In-Reply-To: <15150.1141246521@sss.pgh.pa.us>
References: <200603012013.k21KDFM21139@candle.pha.pa.us>
<14834.1141244891@sss.pgh.pa.us>
<44060682.9020505@markdilger.com>
<15150.1141246521@sss.pgh.pa.us>
Tom Lane wrote:
> Mark Dilger <pgsql@markdilger.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
I did not mean to imply that the two functions would be calling each other.
Rather, I thought that a user should get sensible results if they called them
both together. The current code (without the patch) behaves as follows:
select justify_days(justify_hours('1 month 95 days -36:00:00'::interval));
justify_days
-------------------------
4 mons 4 days -12:00:00
which seems inconsistent with the intent of the patch. Shouldn't the patched
version return '4 mons 3 days 12:00:00' instead?
mark
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@markdilger.com, tgl@sss.pgh.pa.us, pgman@candle.pha.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [SQL] Interval subtracting
In-Reply-To: <44060C42.50905@markdilger.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox