Обсуждение: order of adding date & interval values?

hello,

using 7.2.1

is there any reason why the order of operations of the following query
would matter?

here's an example:

diw=# select interval '40 years' +  date '2001-01-01' as test;
        test
---------------------
 2001-01-01 00:00:00
(1 row)

diw=# select date '2001-01-01' + interval '40 years' as test;
        test
---------------------
 2041-01-01 00:00:00
(1 row)

note how the first query doesn't return the proper response.


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

or

"date('2001-01-01')" and "date '2001-01-01' " ?

because only the query at the top of this message works, the following
doesn't:

diw=# select date('2001-01-01') + interval('40 years') as test;
ERROR:  parser: parse error at or near "'"

I realize that answers my question, but why?

any help would be appreciated!!!

Postgresql expects a date first from what i am seeing. You can't just have
40 years then add a date because what is the 40 years.

You are adding a specific date to 40 years which the parser says is not
correct and logically this is true.

You should always have the date first before you add any interval of time
to it.

The difference between date('2001-01-01') and date '2001-01-01' is this

date('2001-01-01') Postgres is thinking this is a function defined in the
database. So you will get the error message.

This is my 2 cents

HTH

Darren Ferguson

On Thu, 2 May 2002, Lev Lvovsky wrote:

> hello,
>
> using 7.2.1
>
> is there any reason why the order of operations of the following query
> would matter?
>
> here's an example:
>
> diw=# select interval '40 years' +  date '2001-01-01' as test;
>         test
> ---------------------
>  2001-01-01 00:00:00
> (1 row)
>
> diw=# select date '2001-01-01' + interval '40 years' as test;
>         test
> ---------------------
>  2041-01-01 00:00:00
> (1 row)
>
> note how the first query doesn't return the proper response.
>
>
> also, is there a difference between:
> "interval('40 years') " and "interval '40 years' " ?
>
> or
>
> "date('2001-01-01')" and "date '2001-01-01' " ?
>
> because only the query at the top of this message works, the following
> doesn't:
>
> diw=# select date('2001-01-01') + interval('40 years') as test;
> ERROR:  parser: parse error at or near "'"
>
> I realize that answers my question, but why?
>
> any help would be appreciated!!!
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

> is there any reason why the order of operations of the following query
> would matter?

Yes. But not a good one :(

> diw=# select interval '40 years' +  date '2001-01-01' as test;
> ---------------------
>  2001-01-01 00:00:00

This is relying on the interval being turned into a time field, which
gets modulo'd by 24 hours. We should probably check the conversion and
reject anything which needs modulo to fit into 24 hours, and we should
probably not allow this particular implicit coersion by defining an
explicit operator for these two data types in this order.

We used to have the ability to reorder the arithmetic to get the correct
answer, but afaicr that was removed since we were apparently misusing
fields to accomplish this.

> also, is there a difference between:
> "interval('40 years') " and "interval '40 years' " ?
> or
> "date('2001-01-01')" and "date '2001-01-01' " ?
> diw=# select date('2001-01-01') + interval('40 years') as test;
> ERROR:  parser: parse error at or near "'"

In 7.2.x, interval(int) is the specifier for a data type, not a function
call. The same new feature is available for timestamp. So you can't use
the function call form for type conversion anymore (at least not without
gymnastics). Use

interval '40 years'

instead.

                     - Thomas

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

...
>         '40 years'::interval
>         CAST('40 years' as interval)
> The former is succinct, the latter SQL-standard ...

as is

  interval '40 years'

(give or take some suffix tokens).

                     - Thomas

Good lord!  a helpful mailing list!

Wow, thanks for all the help!

-lev (with more questions soon to come!)

On Thu, 2 May 2002, Tom Lane wrote:

> 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.
[snip]