On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
