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.