# Re: [HACKERS] sign function with INTERVAL?

```On 4/13/16 1:36 PM, Daniel Lenski wrote:
```
```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)
```
```
```
The only thing that comes to mind is you can get some strange circumstances with intervals, like '-1 mons +4 days'. I don't think that precludes sign() though.
```
```
```What I have come up with is this rather inelegant and error-prone case
statement:
```
```
How is it error prone?

```
```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
```
```
You don't need to handle null explicitly. You could do

```
SELECT CASE WHEN x > interval '0' THEN 1 WHEN x < interval '0' THEN -1 WHEN x = interval '0' THEN 0 END
```
Or, you could do...

```
CREATE FUNCTION sign(interval) RETURNS int LANGUAGE sql STRICT IMMUTABLE AS \$\$ SELECT CASE WHEN \$1 > interval '0' THEN 1 WHEN x < interval '0' THEN -1 ELSE 0 END
```\$\$;

```
That works because a STRICT function won't even be called if any of it's inputs are NULL.
```

```
```Is there a more obvious way to do sign(interval)? Would it be
technically difficult to make it "just work"?
```
```
```
Actually, after looking at the code for interval_lt, all that needs to happen to add this support is to expose interval_cmp_internal() as a strict function. It already does exactly what you want.
```--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
```