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
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 |
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 |