Re: order of adding date & interval values?

Lev Lvovsky <lists1(at)sonous(dot)com> writes:
> is there any reason why the order of operations of the following query
> would matter?

Perusing the list of operators shown by "\do +" reveals that there's
a date + interval operator, but no interval + date operator. So your
interval + date example is getting interpreted in some surprising
fashion involving an implicit cast.

One way to find out exactly what the system is doing is:

test72=# create view vv as
test72-# select interval '40 years' + date '2001-01-01' as test;
CREATE
test72=# \d vv
View "vv"
Column | Type | Modifiers
--------+-----------------------------+-----------
test | timestamp without time zone |
View definition: SELECT ('00:00:00'::"time" + '2001-01-01'::date) AS test;

test72=#

which leads to the conclusion that the system is picking the time + date
operator, and coercing "interval '40 years'" to time in order to do it.
Unfortunately the time-of-day portion of exactly 40 years is 0.

In current development sources I got an error on your example instead of
a weird answer, because interval-to-time is no longer allowed as an
implicit coercion. The above example shows why I consider it a good
idea to rein in implicit coercions...

> also, is there a difference between:
> "interval('40 years') " and "interval '40 years' " ?

Yes, the former gives a syntax error ;-). This is because interval(n)
is now a type name, per SQL spec (n is the precision).

You can still do it if you double-quote the type name:
"interval"('40 years')
but it might be better to switch over to the better-supported cast
syntaxes, either of
'40 years'::interval
CAST('40 years' as interval)
The former is succinct, the latter SQL-standard ...

regards, tom lane