pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joseph Barillari <jbarilla@princeton.edu>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Odd behavior with timestamp/interval arithmetic
Date: Fri, 03 May 2002 16:38:14 -0400
Message-ID: <m3wuulj7bd.fsf@washer.barillari.org> (raw)

I'm not very attuned to the subtleties of SQL, but this behavior seems
downright strange:

Adding an interval to a timestamp produces the expected result:

cal=> select timestamp without time zone  'jan 1, 1999 00:00:00' + interval '1 month';  
      ?column?       
---------------------
 1999-02-01 00:00:00
(1 row)

But reversing the two produces nonsense: is this because the values
are implicitly cast to type of the leftmost term in the expression?

cal=> select interval '1 month' + timestamp without time zone  'jan 1, 1999 00:00:00';                       
 ?column? 
----------
 00:00:00
(1 row)

This behavior is perfectly reasonable:

cal=> select timestamp   'jan 1, 1999 00:00:00' + interval '1 month';
        ?column?        
------------------------
 1999-02-01 00:00:00-05
(1 row)

And yet, this expression, which just reverse the terms, is downright
weird:

cal=> select interval '1 month' + timestamp   'jan 1, 1999 00:00:00';
      ?column?       
---------------------
 1999-01-01 00:00:00
(1 row)

Absolutely nothing happens! It is as if the '1 month' term did not
exist. 

Here's the weirdest part:

cal=> select interval '1 month 2:30' + timestamp  'jan 1, 1999 00:00:00';  
      ?column?       
---------------------
 1999-01-01 02:30:00
(1 row)

It picks up the 2:30, but not the 1 month!

Is this the proper behavior? Or is there likely something wrong,
either with PostgreSQL or my installation of it?

Any comments would be appreciated.

Thanks,

Joe Barillari

view thread (2+ messages)  latest in thread

Message-ID: <m3wuulj7bd.fsf@washer.barillari.org>
Permalink:  ../m3wuulj7bd.fsf@washer.barillari.org/
Also on:    postgresql.org/message-id/m3wuulj7bd.fsf@washer.barillari.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jbarilla@princeton.edu
  Subject: Re: Odd behavior with timestamp/interval arithmetic
  In-Reply-To: <m3wuulj7bd.fsf@washer.barillari.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox