sign function with INTERVAL?

From: Daniel Lenski <dlenski(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: sign function with INTERVAL?
Date: 2016-04-13 18:36:21
Message-ID: CAOw_LSGdwc+BjudvZ3gme3_c2toXHKwzDoAZpSZXcvHUt6cppQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gianni Ceccarelli 2016-04-13 18:54:56 Re: sign function with INTERVAL?
Previous Message David G. Johnston 2016-04-13 17:22:36 Re: Freezing localtimestamp and other time function on some value

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2016-04-13 18:52:15 Re: Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold <
Previous Message Robert Haas 2016-04-13 18:36:14 Re: Odd system-column handling in postgres_fdw join pushdown patch