Hi all,
Is there a good reason why the SIGN() function does not work with the
INTERVAL type? (It is only defined for numeric types.)
(http://www.postgresql.org/docs/9.5/static/functions-math.html)
select sign(-3); -- okay
select sign(interval '4 years'); -- ERROR: function sign(interval)
does not exist
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.
What I have come up with is this rather inelegant and error-prone case
statement:
case when x is null then null x>interval '0' then +1 when x<interval
'0' then -1 when x=interval '0' then 0 end
Here's an example:
with t as (select interval '4 years 2 months' as x, interval '-1
minute 2 seconds' as y, interval '0' as z, null::interval as w)
select case when x is null then null when x>interval '0' then +1 when
x<interval '0' then -1 when x=interval '0' then 0 end,
case when y is null then null when y>interval '0' then +1 when
y<interval '0' then -1 when y=interval '0' then 0 end,
case when z is null then null when z>interval '0' then +1 when
z<interval '0' then -1 when z=interval '0' then 0 end,
case when w is null then null when w>interval '0' then +1 when
w<interval '0' then -1 when w=interval '0' then 0 end
from t
Is there a more obvious way to do sign(interval)? Would it be
technically difficult to make it "just work"?
Thanks,
Dan