Re: BUG #1871: operations with data types

On Sun, Sep 11, 2005 at 03:03:57AM -0400, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.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