From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Dilger <pgsql@markdilger.com>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [SQL] Interval subtracting
Date: Wed, 01 Mar 2006 15:55:21 -0500
Message-ID: <15150.1141246521@sss.pgh.pa.us> (raw)
In-Reply-To: <44060682.9020505@markdilger.com>
References: <200603012013.k21KDFM21139@candle.pha.pa.us>
<14834.1141244891@sss.pgh.pa.us>
<44060682.9020505@markdilger.com>
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
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: tgl@sss.pgh.pa.us, pgsql@markdilger.com, pgman@candle.pha.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [SQL] Interval subtracting
In-Reply-To: <15150.1141246521@sss.pgh.pa.us>
* 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