pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOdd behavior with timestamp/interval arithmetic
2+ messages / 2 participants
[nested] [flat]
* Odd behavior with timestamp/interval arithmetic
@ 2002-05-03 20:38 Joseph Barillari <jbarilla@princeton.edu>
2002-05-03 21:59 ` Re: Odd behavior with timestamp/interval arithmetic Josh Berkus <agliodbs@openoffice.org>
0 siblings, 1 reply; 2+ messages in thread
From: Joseph Barillari @ 2002-05-03 20:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Odd behavior with timestamp/interval arithmetic
2002-05-03 20:38 Odd behavior with timestamp/interval arithmetic Joseph Barillari <jbarilla@princeton.edu>
@ 2002-05-03 21:59 ` Josh Berkus <agliodbs@openoffice.org>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2002-05-03 21:59 UTC (permalink / raw)
To: Joseph Barillari <jbarilla@princeton.edu>; pgsql-sql
Joe,
> 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?
No. It's a bug. The expression interval + timestamp was accidentally
left undefined in PostgreSQL 7.2.
You can fix it by using a CREATE OPERATOR statement to define Interval
+ timestamp.
-Josh Berkus
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-03 21:59 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-03 20:38 Odd behavior with timestamp/interval arithmetic Joseph Barillari <jbarilla@princeton.edu>
2002-05-03 21:59 ` Josh Berkus <agliodbs@openoffice.org>
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