Re: Absolute value of intervals

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'.
2.1) negating '10' gives '-10'.
2.2) negating the other gives '-1 hundred 90 units'.
3) give 'hundred' the value of '100' and 'units' the value '1' and
check if things sum up.

If the absolute value of an interval was defined to strip out all the
negation signs you'd get the "wrong" answers out. The awkward thing
with intervals is the the components are not all of the same units, but
I think the argument stands.

--
Sam http://samason.me.uk/