Skip site navigation (1) Skip section navigation (2)

Re: BUG #1871: operations with data types

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: anris(at)polynet(dot)lviv(dot)ua, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1871: operations with data types
Date: 2005-09-13 13:10:16
Message-ID: 20050913131016.GA3732@winnie.fuhr.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-09-13 13:54:16
Subject: Re: BUG #1871: operations with data types
Previous:From: Paul O'ConnorDate: 2005-09-13 11:48:56
Subject: BUG #1879: ODBC Connection Dies

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group