Re: BUG #1871: operations with data types

On Sun, Sep 11, 2005 at 03:03:57AM -0400, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > What do other DBMSs do?
>
> This is a fair question. Given that the SQL committee hasn't provided
> any useful leadership, what are other groups doing?

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