Re: Absolute value of intervals

From: Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
To: Scott Bailey <artacus(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Absolute value of intervals
Date: 2009-10-31 11:52:01
Message-ID: 0301BD70-462C-44E9-9E17-6E20EB28801E@solfertje.student.utwente.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 30 Oct 2009, at 21:09, Scott Bailey wrote:

>> My personal feeling is that when you provide any ordering operator
>> and
>> negation you can easily provide an absolute value operator. We've
>> already (somewhat arbitrarily) decided that one of '1month -30days'
>> and
>> '-1month 30days) is "greater" than the other, so why not provide an
>> operator that returns the "greater" of an interval value and its own
>> negation?
>
> Technically, greater doesn't arbitrarily decide one is greater than
> the other. It determines the two are equivalent and (correctly)
> chooses the leftmost one.
>
> I think it is important to separate the concept of an interval with
> addition of an interval with a timestamp. By (the interval type's)
> definition a day is 24 hours, a month is 30 days, a year is 365.25
> days. And the user needs to understand that abs and extract epoch do
> their calculations based on those definitions rather than what would
> happen when applied to an arbitrary timestamp.

There's a slight complication to this approach; what happens if you
ask for <timestamp> + abs(<interval>)?

You don't want to calculate the result of abs() based on a 24h day, a
30d month and a 365.25d year as there is a timestamp to base your
calculations on, but AFAIK you can't see that from within the abs()
function implementation. Unless you store that information in the
context somehow.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4aec24e711071499813979!

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2009-10-31 13:21:59 Re: Possible to UPDATE array[] columns?
Previous Message Guillaume Lelarge 2009-10-31 06:46:48 Re: pg_start_backup question