Re: Absolute value of intervals

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)