Re: '1 year' = '360 days' ???? - Mailing list pgsql-general

    problem is that '1 months':: interval does not have the same value if you
add it to a date or another :

=> SELECT '2004-02-01'::timestamp+'1 month'::interval,
'2004-03-01'::timestamp+'1 month'::interval;
       ?column?       |      ?column?
---------------------+---------------------
  2004-03-01 00:00:00 | 2004-04-01 00:00:00

SELECT '2004-03-01'::timestamp-'2004-02-01'::timestamp,
'2004-04-01'::timestamp-'2004-03-01'::timestamp;
  ?column? | ?column?
----------+----------
  29 days  | 31 days

    That's because a month is an undefined number of days (also some years
are 366 days). In that case '1 months':: interval is either 29 or 31 days
but it could be 28 in february 2003 or 30 in april !

    Thus if we have a date d and two intervals i1 and i2 :

    The comparison (d+i1) < (d+i2) depends on the value of d (and the
timezone).
    For instance if i1 is '1 month' and i2 is '30 days', we have :

SELECT '2004-02-01'::timestamp+'1 month'::interval,
'2004-02-01'::timestamp+'30 days'::interval;
       ?column?       |      ?column?
---------------------+---------------------
  2004-03-01 00:00:00 | 2004-03-02 00:00:00

    Thus (d+i1) < (d+i2)


SELECT '2004-04-01'::timestamp+'1 month'::interval,
'2004-04-01'::timestamp+'30 days'::interval;
       ?column?       |      ?column?
---------------------+---------------------
  2004-05-01 00:00:00 | 2004-05-01 00:00:00

    Thus (d+i1) = (d+i2)

SELECT '2004-03-01'::timestamp+'1 month'::interval,
'2004-03-01'::timestamp+'30 days'::interval;
       ?column?       |      ?column?
---------------------+---------------------
  2004-04-01 00:00:00 | 2004-03-31 00:00:00

    Thus (d+i1) > (d+i2)

    And that's normal ! Intervals having months are extremely useful to
express the idea of 'same day, next month' that you can't do with just an
interval expressed in seconds. However, beware :

SELECT '2004-01-31'::timestamp+'1 month'::interval;
       ?column?
---------------------
  2004-02-29 00:00:00
(1 ligne)

SELECT '2004-01-30'::timestamp+'1 month'::interval;
       ?column?
---------------------
  2004-02-29 00:00:00
(1 ligne)

SELECT '2004-01-29'::timestamp+'1 month'::interval;
       ?column?
---------------------
  2004-02-29 00:00:00
(1 ligne)

SELECT '2004-01-28'::timestamp+'1 month'::interval;
       ?column?
---------------------
  2004-02-28 00:00:00


    31 january + 1 month = 29 february (it clips at the end of the month,
which is IMHO GOOD).

    How can we sort intervals meaningfully in these conditions ? Can we ? In
fact the value of an interval depends on the application, and intervals
with months are in another 'world' than intervals with only seconds...
same thing for years.


>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>