From: | Joseph Barillari <jbarilla(at)princeton(dot)edu> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Odd behavior with timestamp/interval arithmetic |
Date: | 2002-05-03 20:38:14 |
Message-ID: | m3wuulj7bd.fsf@washer.barillari.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph Barillari | 2002-05-03 21:24:40 | Returning composite types from a plpgsql function |
Previous Message | Josh Berkus | 2002-05-02 22:38:22 | Re: Subselect performance question |