Lev Lvovsky <lists1@sonous.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
