Обсуждение: timestamp issue

Hello all,

I am running PostgreSQL 9.0.2 on Mac (10.6.6)

This is what I get in the psql terminal:

postgres=3D# SELECT version();
                                                              version=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
---------------------------------------------------------------------------=
---------------------------------------------------------
 PostgreSQL 9.0.2 on x86_64-apple-darwin, compiled by GCC i686-apple-darwin=
10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit
(1 row)

postgres=3D# SELECT now() + interval '7 days' AS week_ahead;
            week_ahead=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------
 2011-02-24 10:39:07.071655+05:30
(1 row)

postgres=3D# SELECT now() - interval '7 days' AS week_behind;
           week_behind=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------
 2011-02-10 10:39:21.848049+05:30
(1 row)

postgres=3D# SELECT now() + '7 days' AS week_ahead;
            week_ahead=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------
 2011-02-24 10:39:29.951931+05:30
(1 row)

postgres=3D# SELECT now() - '7 days' AS week_behind;
ERROR:  invalid input syntax for type timestamp with time zone: "7 days"
LINE 1: SELECT now() - '7 days' AS week_behind;
                                            ^

Now, if '+' operator  works with '7 days', why dint the '-' operator? Is th=
is intentional or a bug?

--
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise PostgreSQL company.

On 17.02.2011 08:06, Sachin Srivastava wrote:
> postgres=# SELECT now() + '7 days' AS week_ahead;
>              week_ahead
> ----------------------------------
>   2011-02-24 10:39:29.951931+05:30
> (1 row)
>
> postgres=# SELECT now() - '7 days' AS week_behind;
> ERROR:  invalid input syntax for type timestamp with time zone: "7 days"
> LINE 1: SELECT now() - '7 days' AS week_behind;
>                                              ^
>
> Now, if '+' operator  works with '7 days', why dint the '-' operator? Is this intentional or a bug?

Intentional, or at least coincidental.

postgres=# SELECT oprname, oprleft::regtype, oprright::regtype FROM
pg_operator WHERE oprleft ='timestamptz'::regtype AND oprname IN('+', '-');
  oprname |         oprleft          |         oprright
---------+--------------------------+--------------------------
  +       | timestamp with time zone | interval
  -       | timestamp with time zone | timestamp with time zone
  -       | timestamp with time zone | interval
(3 rows)

With '-', it's getting interpreted as timestamptz-timestamptz.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> On 17.02.2011 08:06, Sachin Srivastava wrote:
>> postgres=# SELECT now() - '7 days' AS week_behind;
>> ERROR:  invalid input syntax for type timestamp with time zone: "7 days"
>> Now, if '+' operator  works with '7 days', why dint the '-' operator? Is this intentional or a bug?

> Intentional, or at least coincidental.

> postgres=# SELECT oprname, oprleft::regtype, oprright::regtype FROM
> pg_operator WHERE oprleft ='timestamptz'::regtype AND oprname IN('+', '-');
>   oprname |         oprleft          |         oprright
> ---------+--------------------------+--------------------------
>   +       | timestamp with time zone | interval
>   -       | timestamp with time zone | timestamp with time zone
>   -       | timestamp with time zone | interval
> (3 rows)

> With '-', it's getting interpreted as timestamptz-timestamptz.

To be more specific: since you didn't cast the unknown-type literal to
any particular type, the parser had to guess which operator is meant.
In a case like this it will prefer to guess that both operands are of
the same datatype.  So, knowing that now() yields timestamptz, it tried
to interpret the literal as timestamptz too.

There is no corresponding problem on the + side because there's no
timestamptz+timestamptz operator, that not being a meaningful operation.

            regards, tom lane