Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp issue

timestamp issue

From
Sachin Srivastava
Date:
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.

Re: timestamp issue

From
Heikki Linnakangas
Date:
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

Re: timestamp issue

From
Tom Lane
Date:
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