The following bug has been logged online:

Bug reference: 1871
Logged by:
Email address: anris (AT) polynet (DOT) lviv.ua
PostgreSQL version: 7-8
Operating system: Linux
Description: operations with data types
Details:

May be it is not bug, but anywhere:

here you are sample query
select '2005-08-31'::date + '1 month'::interval-'1 month'::interval

from the mathematical me the resulting value should be '2005-08-31', but
.......

can you explain this

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, Sep 09, 2005 at 01:00:31PM +0100, anris (AT) polynet (DOT) lviv.ua wrote:
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

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Well, no, because + and - associate left-to-right.

This seems to be the crux of the issue: is the above expression valid
and if so what should it yield?

I think you are right that the SQL spec wants it to raise an error,
but the spec's rules about datetime behavior are uselessly narrow
minded (last I checked, they still had not heard of daylight savings
time ... so they're obviously not trying very hard in this area).

The behavior that's in our code now is to round back to the last real
day of the month. This might not be the best choice, but raising an
error doesn't seem better to me offhand. Date and Darwen seem to
think rounding forward to the first day of the next month would be
more natural. I'm not sure why; it certainly wouldn't fix the
complainant's issue, only surprise him in a different way. Also,
even if you like round-forward for the above case, what about
subtraction --- what should '2005-10-31' - '1 month' give? Rounding
down definitely feels more natural in that case, at least to me.

Any comments out there?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Sun, Sep 11, 2005 at 12:43:58AM -0400, Tom Lane wrote:
Sure, I wasn't expecting any different -- I was just mentioning it
for the mathematically inclined who might think +1 and -1 should
cancel each other out.

Dunno. It does seem inconsistent that these expressions give the
same answer:

select '2005-08-30'::date + '1 month'::interval,
'2005-08-30'::date + '1 day'::interval + '1 month'::interval;
?column? | ?column?
---------------------+---------------------
2005-09-30 00:00:00 | 2005-09-30 00:00:00
(1 row)

and these give different answers:

select '2005-08-30'::date + '1 day'::interval + '1 month'::interval,
'2005-08-30'::date + '1 month'::interval + '1 day'::interval;
?column? | ?column?
---------------------+---------------------
2005-09-30 00:00:00 | 2005-10-01 00:00:00
(1 row)

but I doubt I could make an argument for an alternative that was
any better than its counterargument.

Wait a minute, here's proof that it *must* be wrong ;-)

mysql> select date_add('2005-08-31', interval 1 month);
+------------------------------------------+
1 row in set (0.11 sec)

What do other DBMSs do?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
This is certainly an arena in which you can't expect the commutative
or associative laws to hold. For instance:

('2005-02-28' + '1 day') + '1 month'

You can't possibly argue that that produces anything but
'2005-03-01' + '1 month' and thence '2005-04-01'. On the other
hand, you can't possibly argue that

('2005-02-28' + '1 month') + '1 day'

produces anything but '2005-03-28' + '1 day' and thence '2005-03-29'.
So the original complaint can at best be described as lacking thought.

rotfl ;-)

This is a fair question. Given that the SQL committee hasn't provided
any useful leadership, what are other groups doing?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Sun, Sep 11, 2005 at 03:03:57AM -0400, Tom Lane wrote:
I don't have access to an Oracle system, but the following page has
examples:

http://philip.greenspun.com/sql/dates

It looks like Oracle has "old" and "new" (version 9 and later?)
ways of doing the query and they give different results:

-- old
select add_months(to_date('2003-07-31','YYYY-MM-DD'),-1) from dual;

ADD_MONTHS
----------
2003-06-30

-- new
select to_timestamp('2003-07-31','YYYY-MM-DD') - interval '1' month from dual;

ERROR at line 1:
ORA-01839: date not valid for month specified

I just noticed something in PostgreSQL that might be considered
surprising (although I do see "Add ISO INTERVAL handling" in the
TODO list):

test=> select date '2005-08-01' + interval '1 month';
?column?
---------------------
2005-09-01 00:00:00
(1 row)

test=> select date '2005-08-01' + interval '1' month;
?column?
---------------------
2005-08-01 00:00:00
(1 row)

test=> select interval '1' month;
interval
----------
00:00:00
(1 row)

What's the parser doing here?

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Michael Fuhr <mike (AT) fuhr (DOT) org> writes:
Not getting it right ;-). Trying this in historical versions is
amusing:

7.0:
regression=# select interval '1' month;
ERROR: parser: parse error at or near "month"

7.1:
regression=# select interval '1' month;
ERROR: Bad interval external representation '1'

7.2:
regression=# select interval '1' month;
interval
----------
00:00
(1 row)

7.3:
regression=# select interval '1' month;
interval
----------
00:00:01
(1 row)

7.4 and up:
regression=# select interval '1' month;
interval
----------
00:00:00
(1 row)

What is happening in the current versions is that coerce_type thinks
it can coerce the literal string to interval without supplying the
modifier, and then use interval_scale() to apply the typmod. This
works OK for most of the data types, but not for interval it seems...

Basically the support for these weird syntaxes is something that Tom
Lockhart never finished, and no one has bothered to pick up the work
since he left the project.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Added to TODO:

o Fix SELECT INTERVAL '1' MONTH;

---------------------------------------------------------------------------

Tom Lane wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org