Re: sign function with INTERVAL?

On 2016-04-13 Daniel Lenski <dlenski(at)gmail(dot)com> wrote:
> Hi all,
> I'm trying to find a straightforward and reliable way to differentiate
> positive, negative, and zero time intervals while handling NULL in the
> same way as the SIGN() function.

I'm not sure that "positive time interval" is a thing. Witness:

dakkar=> select interval '1 month - 30 days' > interval '0';
┌──────────┐
│ ?column? │
├──────────┤
│ f │
└──────────┘
(1 row)

not positive? maybe it's negative?

dakkar=> select interval '1 month - 30 days' < interval '0';
┌──────────┐
│ ?column? │
├──────────┤
│ f │
└──────────┘
(1 row)

no, not negative either. Why? Well…

dakkar=> select date '2016-03-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-03-02 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks positive, but

dakkar=> select date '2016-02-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-01-31 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks negative.

So I suspect the reason SIGN() is not defined for intervals is that
it cannot be made to work in the general case.

--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

Thrashing is just virtual crashing.