Re: sign function with INTERVAL?

On 2016-04-13 Daniel Lenski <dlenski@gmail.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.