Postgres Pro
Downloads
Home   >   mailing lists

Thread: Oddity with literal intervals

Oddity with literal intervals

From
Mike Mascari
Date:
Here's something odd I stumbled upon:

[estore@lexus] select now() + '1 day';
            ?column?
-------------------------------
  2004-03-19 12:19:44.997344-05
(1 row)

[estore@lexus] select now() - '1 day';
ERROR:  invalid input syntax for type timestamp with time zone: "1 day"
[estore@lexus] select now() + '-1 day';
            ?column?
-------------------------------
  2004-03-17 12:20:56.287847-05
(1 row)

[estore@lexus] select version();
                                                  version

---------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.2 20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)

It seems odd...

Mike Mascari

Re: Oddity with literal intervals

From
Tom Lane
Date:
Mike Mascari <mascarm@mascari.com> writes:
> Here's something odd I stumbled upon:

> [estore@lexus] select now() - '1 day';
> ERROR:  invalid input syntax for type timestamp with time zone: "1 day"

What's odd about it?  The preferred interpretation is timestamptz minus
another timestamptz (yielding an interval).  If you want timestamptz
minus interval (yielding timestamptz), you have to do something to cue
the system that the literal should be taken as an interval.

The "+" cases work because there is no timestamp plus timestamp operator.

            regards, tom lane

Re: Oddity with literal intervals

From
Mike Mascari
Date:
Tom Lane wrote:

> Mike Mascari <mascarm@mascari.com> writes:
>>Here's something odd I stumbled upon:
>
>>[estore@lexus] select now() - '1 day';
>>ERROR:  invalid input syntax for type timestamp with time zone: "1 day"
>
> What's odd about it?  The preferred interpretation is timestamptz minus
> another timestamptz (yielding an interval).  If you want timestamptz
> minus interval (yielding timestamptz), you have to do something to cue
> the system that the literal should be taken as an interval.
>
> The "+" cases work because there is no timestamp plus timestamp operator.

Okay. That's why I posted it to -general and not -bugs, because I
suspected there was some reason behind it. I guess it seemed odd
because it has been on rare occasion that I have encountered types
where there exists a '-' operator without a corresponding '+'
operator. And, coincidentally having a '+' operator available for
timestampz + interval just added to my confusion. But the above
makes perfect sense.

Thanks!

Mike Mascari