precise interval documentation

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: pgsql-general(at)postgresql(dot)org
Subject: precise interval documentation
Date: 2001-01-13 15:44:46
Message-ID: 20010113094446.B25207@wolff.to
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Because of leap days and their two part nature, there are some aspects of
intervals that should be covered more precisely in the documentation.

Example 1:
area=> select '1999-02-28'::timestamp + '1 day 1 year';
?column?
------------------------
2000-02-29 00:00:00-06

This indicates that the month-year offset of an interval is added before the
day offset. Is this always the case?

Example 2:
area=> select '2000-02-29'::timestamp - '1999-02-28';
?column?
------------
366 +00:00

This suggests that the difference between to timestamps returns an interval
with a 0 year-month part. Is this always the case?

Example 3:
area=> select '2000-02-29'::timestamp + '1 year';
?column?
------------------------
2001-02-28 00:00:00-06

This suggests that when a year-month offset results in a day of February 29
in a now leap year, that February 28 is returned instead. Is this always the
case?

Example 4:
area=> select '1999-03-01 1:00 UTC'::timestamp + '1 year';
?column?
------------------------
2000-02-28 19:00:00-06

This suggests that the year-month offset is applied to the timestamp
represented in local time zone. Is this always the case?

Browse pgsql-general by date

  From Date Subject
Next Message gianpaolo 2001-01-13 19:54:16 Functions: passing table names as parameters
Previous Message Bruno Wolff III 2001-01-13 15:19:07 Re: ORDER BY works stranke