sign function with INTERVAL?

On 2016-04-13 Daniel Lenski <dlenski@gmail.com> wrote:

Hi all,
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.

I'm not sure that "positive time interval" is a thing. Witness:

dakkar=> select interval '1 month - 30 days' > interval '0';
┌──────────┐
│ ?column? │
├──────────┤
│ f │
└──────────┘
(1 row)

not positive? maybe it's negative?

dakkar=> select interval '1 month - 30 days' < interval '0';
┌──────────┐
│ ?column? │
├──────────┤
│ f │
└──────────┘
(1 row)

no, not negative either. Why? Well…

dakkar=> select date '2016-03-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-03-02 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks positive, but

dakkar=> select date '2016-02-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-01-31 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks negative.

So I suspect the reason SIGN() is not defined for intervals is that
it cannot be made to work in the general case.

--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

Thrashing is just virtual crashing.

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

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

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.

interval_cmp() is already SQL-accessible.

regards, tom lane

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

On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

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.

interval_cmp() is already SQL-accessible.

Thanks! The interval_cmp() function does not appear in the 9.5 docs.
http://www.postgresql.org/docs/9.5/static/functions-datetime.html

On Wed, Apr 13, 2016 at 11:54 AM, Gianni Ceccarelli
<dakkar@thenautilus.net> wrote:

I'm not sure that "positive time interval" is a thing. Witness:

(snip)

dakkar=> select date '2016-03-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-03-02 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks positive, but

dakkar=> select date '2016-02-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-01-31 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks negative.

So I suspect the reason SIGN() is not defined for intervals is that
it cannot be made to work in the general case.

I hadn't considered this case of an interval like '1 month - 30 days',
which could be either positive or negative depending on the starting
date to which it is added.

interval_cmp's handling of this case seems surprising to me. If I've
got this right, it assumes that (interval '1 month' == interval '30
days') exactly:
http://doxygen.postgresql.org/backend_2utils_2adt_2timestamp_8c_source.html#l02515

Do I have that right? I'm having trouble envisioning an application
that would ever generate intervals that contain months and days
without opposite signs, but it's useful to know that such a corner
case could exist.

Given this behavior, the only 100% reliable way to check whether an
interval is forward, backwards, or zero would be to first add, and
then subtract, the starting point:

postgres=# select interval_cmp( (date '2016-02-01' + interval '1 month
- 30 days') - date '2016-02-01', interval '0' );
interval_cmp
--------------
-1
(1 row)

postgres=# select interval_cmp( (date '2016-04-01' + interval '1 month
- 30 days') - date '2016-04-01', interval '0' );
interval_cmp
--------------
0
(1 row)

Thanks,
Dan

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

On Wed, Apr 13, 2016 at 3:48 PM, Daniel Lenski <dlenski@gmail.com> wrote:

On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:

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.

interval_cmp() is already SQL-accessible.

Thanks! The interval_cmp() function does not appear in the 9.5 docs.
http://www.postgresql.org/docs/9.5/static/functions-datetime.html

On Wed, Apr 13, 2016 at 11:54 AM, Gianni Ceccarelli
<dakkar@thenautilus.net> wrote:

I'm not sure that "positive time interval" is a thing. Witness:

(snip)

dakkar=> select date '2016-03-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-03-02 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks positive, but

dakkar=> select date '2016-02-01' + interval '1 month - 30 days';
┌─────────────────────┐
│ ?column? │
├─────────────────────┤
│ 2016-01-31 00:00:00 │
└─────────────────────┘
(1 row)

when used this way, it looks negative.

So I suspect the reason SIGN() is not defined for intervals is that
it cannot be made to work in the general case.

I hadn't considered this case of an interval like '1 month - 30 days',
which could be either positive or negative depending on the starting
date to which it is added.

interval_cmp's handling of this case seems surprising to me. If I've
got this right, it assumes that (interval '1 month' == interval '30
days') exactly:

http://doxygen.postgresql.org/backend_2utils_2adt_2timestamp_8c_source.html#l02515

​I was trying to figure out how the months/year fit in here - or whether
years are derived from days (i.e., 365 instead of 360)...​

The anchored section of code only shows stand-alone conversion factors for
days->hours and months-days

Do I have that right? I'm having trouble envisioning an application
that would ever generate intervals that contain months and days
without opposite signs, but it's useful to know that such a corner
case could exist.

Yes.
&
​I want the date that is 1 month and 14 days (2 weeks) from now...

For added fun the SQL standard apparently disallows mixed signs (according
to our docs)

​According to the SQL standard all fields of an interval value must have

the same sign,

so a leading negative sign applies to all fields; for example the negative

sign in

the interval literal '-1 2:03:04' applies to both the days and

hour/minute/second parts.

​http://www.postgresql.org/docs/current/static/datatype-datetime.html​

Given this behavior, the only 100% reliable way to check whether an
interval is forward, backwards, or zero would be to first add, and
then subtract, the starting point:

postgres=# select interval_cmp( (date '2016-02-01' + interval '1 month
- 30 days') - date '2016-02-01', interval '0' );
interval_cmp
--------------
-1
(1 row)

postgres=# select interval_cmp( (date '2016-04-01' + interval '1 month
- 30 days') - date '2016-04-01', interval '0' );
interval_cmp
--------------
0
(1 row)

​Yes, the dual nature of an interval, i.e., an assumed conversion factor
(1m = 30d) if dealing with it independently but ​a conversion factor based
on reality (feb has 28 days, typically) makes working with it complicated.
There is not way you could write an operator that successfully handles the
later situation since you cannot write a custom ternary operator that would
take two intervals and a date. That said we already have rules that allow
us to canonical-ize an interval so any form of two-interval comparison can
be performed: but those results become invalidated if one has to apply the
interval to a date.

In short, adding this feature would make it much easier for the
inexperienced to use intervals unsafely without realizing it. It is
possible to write custom functions that do exactly what is needed based
upon the usage of intervals within the system under observation. Doability
combined with ignorance hazard means that the status-quo seems acceptable.

​I guess it would be nice to expose our conversion factors in such a way
that a user can readily get the number of seconds represented by a given
interval when considered without respect to a starting date. But since
most uses of interval are related to dates it seems likely that comparing
intervals by comparing the dates resulting from their application is the
most reliable.

N.B. consider too that the signs are not the whole of it. Intervals allow
for the word "ago" to be specified.

David J.