Re: Absolute value of intervals

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Absolute value of intervals
Date: 2009-11-02 11:16:37
Message-ID: hcmf2l$55l$
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On 2009-10-27, Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:
> On Tue, Oct 27, 2009 at 10:55:31AM -0400, Tom Lane wrote:
>> Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
>> > On Tue, Oct 27, 2009 at 11:27:17AM -0300, Joshua Berry wrote:
>> >> I couldn't find the operator '@' for intervals
>> > A simple SQL implementation would look like:
>> > CREATE FUNCTION absinterval(interval) RETURNS interval
>> > IMMUTABLE LANGUAGE sql AS 'SELECT greatest($1,-$1)';
>> > CREATE OPERATOR @ ( PROCEDURE = absinterval, RIGHTARG = interval );
>> I think this came up again recently and somebody pointed out that the
>> correct definition isn't as obvious as all that.
> Hum, I think it is! :)
>> The components of
>> an interval can have different signs, so should abs('-1 day 1 hour') be
>> '1 day -1 hour' or '1 day 1 hour'? Or what about corner cases like
>> '1 day -25 hours'?
> Funny, I used exactly that example when playing---although I spelled it
> '-1 day 25:00:00'!
> It all comes down to how you define things. I'd say my quick hack does
> the "right" thing, but yes I should have pointed out that the interval
> type has subs-structure that makes it's behavior non-obvious. My
> intuition as to why it's correct worked along these lines:
> 1) '10' can be defined as '1 hundred -90 units'.

not all days are 24 hours long,
some differ by one second or one hour from that.
months are even worse.

> If the absolute value of an interval was defined to strip out all the
> negation signs you'd get the "wrong" answers out.

I think a definition that defines abs(interval)

such that

extract( date-part from abs(interval)) = abs(extract( date-part from interval))

is not totally without merit.

> The awkward thing
> with intervals is the the components are not all of the same units, but
> I think the argument stands.

the awkward thing is that the units are not all related by fixed ratios.

60 minutes per hour
7 days per week
12 months per year

thses are not:

days per month, (gregorian calendar)
days per year, (leap year)
hours per day, (daylight saving)
and seconds per minute (leap second)

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jasen Betts 2009-11-02 11:22:00 Re: Absolute value of intervals
Previous Message Jasen Betts 2009-11-02 10:52:40 Re: Absolute value of intervals