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:0031 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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
