Re: sign function with INTERVAL?

On 2016-04-13 Daniel Lenski  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 - 
	GPG public key fingerprint = A071 E618 DD2C 5901 9574
	                             6FE2 40EA 9883 7519 3F88
	                    key id = 0x75193F88

Thrashing is just virtual crashing.