On Wed, Apr 13, 2016 at 3:48 PM, Daniel Lenski <dlen...@gmail.com> wrote:
> On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > Jim Nasby <jim.na...@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 > <dak...@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.
