Re: Absolute value of intervals - Mailing list pgsql-general

> My personal feeling is that when you provide any ordering operator and
> negation you can easily provide an absolute value operator.  We've
> already (somewhat arbitrarily) decided that one of '1month -30days' and
> '-1month 30days) is "greater" than the other, so why not provide an
> operator that returns the "greater" of an interval value and its own
> negation?

Technically, greater doesn't arbitrarily decide one is greater than the
other. It determines the two are equivalent and (correctly) chooses the
leftmost one.

I think it is important to separate the concept of an interval with
addition of an interval with a timestamp. By (the interval type's)
definition a day is 24 hours, a month is 30 days, a year is 365.25 days.
And the user needs to understand that abs and extract epoch do their
calculations based on those definitions rather than what would happen
when applied to an arbitrary timestamp.

To say that extract epoch can determine the number of seconds in an
interval, while saying that you can not determine the absolute value of
an interval is not logical. Either you can do both or you can do neither.

Postgres intervals internally have an 8 byte microsecond part, a 4 byte
day part and a 4 byte month part. I would argue that there is no
ambiguity with the second (technically microsecond), and day parts of
intervals and that ambiguity is introduced with the month part. A day is
always 24 hours UTC. (However some times our timezones change.) And we
ignore leap seconds. All intervals that result timestamp subtraction
ONLY use the microsecond and day pieces in the resulting interval. This
is probably why most other databases have two interval types. One for
storing precise intervals (DAY TO SECOND) and one for fuzzy intervals
(YEAR TO MONTH).

Now I think that Postgres' interval implementation is much nicer to work
  with than the others. But perhaps things like extract epoch and abs
should exhibit different behaviors when the month part is used.

Consider the following:
SELECT mos,
   EXTRACT(EPOCH FROM INTERVAL '1 month' * mos) / 86400 AS days
FROM generate_series(9, 26) mos;

  mos |  days
-----+--------
    9 |    270
   10 |    300
   11 |    330
   12 | 365.25
   13 | 395.25
   14 | 425.25
   15 | 455.25
   16 | 485.25
   17 | 515.25
   18 | 545.25
   19 | 575.25
   20 | 605.25
   21 | 635.25
   22 | 665.25
   23 | 695.25
   24 |  730.5
   25 |  760.5
   26 |  790.5