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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
