Odd behavior with timestamp/interval arithmetic

Поиск
Список
Период
Сортировка
От Joseph Barillari
Тема Odd behavior with timestamp/interval arithmetic
Дата
Msg-id m3wuulj7bd.fsf@washer.barillari.org
обсуждение исходный текст
Ответы Re: Odd behavior with timestamp/interval arithmetic  ("Josh Berkus" <agliodbs@openoffice.org>)
Список 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

В списке pgsql-sql по дате отправления:

Предыдущее
От:
Дата:
Сообщение: INserting images in postgres
Следующее
От: Joseph Barillari
Дата:
Сообщение: Returning composite types from a plpgsql function