On Fri, Sep 09, 2005 at 01:00:31PM +0100, [EMAIL PROTECTED] wrote:
> select '2005-08-31'::date + '1 month'::interval-'1 month'::interval
>
> from the mathematical me the resulting value should be '2005-08-31'
You didn't show any output; this is what I get:
test=> select '2005-08-31'::date + '1 month'::interval - '1 month'::interval;
?column?
---------------------
2005-08-30 00:00:00
(1 row)
Apparently the two intervals don't cancel each other out (i.e.,
they're not optimized to zero), so effectively we get this:
test=> select '2005-08-31'::date + '1 month'::interval;
?column?
---------------------
2005-09-30 00:00:00
(1 row)
test=> select '2005-09-30 00:00:00'::timestamp - '1 month'::interval;
?column?
---------------------
2005-08-30 00:00:00
(1 row)
I'm wondering if the first expression ('2005-08-31' + '1 month')
should raise an exception. Date & Darwen say it should in _A Guide
to the SQL Standard_, Fourth Edition, p. 276:
....thus, for example, the expression
DATE '1998-08-31' + INTERVAL '1' MONTH
("August 31st, 1998 plus one month") apparently evaluates to
DATE '1998-09-31'
("September 31st, 1998"), and thus fails (an "invalid date"
exception is raised. It does _not_ evaluate (as might perhaps
have been expected) to
DATE '1998-10-01'
("October 1st, 1998"), because such a result would require an
adjustment to the DAY field after the MONTH addition had been
performed. In other words, if interval _i_ is added to date _d_,
and _i_ is of type year-month, then the DAY value in the result
is the same as the DAY value in _d_ (i.e., the DAY value does
not change).
SQL:2003 (draft) Foundation, 6.30 <datetime value expression>,
General Rule 4 says
If the <interval value expression> or <interval term> is a
year-month interval, then the DAY field of the result is the
same as the DAY field of the <datetime term> or <datetime value
expression>.
and General Rule 6b says
If, after the preceding step, any <primary datetime field> of the
result is outside the permissible range of values for the field
or the result is invalid based on the natural rules for dates and
times, then an exception condition is raised: data exception --
datetime field overflow.
Based on these rules, I'd expect '2005-08-31' + '1 month' to evaluate
to '2005-09-31' and thus raise an exception; instead, PostgreSQL
returns '2005-09-30'.
Any standards lawyers out there? Have I misunderstood anything?
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
