Postgres Pro
Downloads
Home   >   mailing lists

Thread: Time intervals in select statement

Time intervals in select statement

From
Luke McFarlane
Date:
Hello

Given the following example table:

CREATE TABLE foo ( create_time TIMESTAMP WITHOUT TIME ZONE );

INSERT INTO foo (create_time) VALUES (now());
INSERT INTO foo (create_time) VALUES (now());
INSERT INTO foo (create_time) VALUES (now());

This select statement works:

SELECT create_time, create_time + '8 days 16:49:22.600601' FROM foo;

       create_time         |          ?column?
----------------------------+----------------------------
 2004-10-19 16:59:31.065279 | 2004-10-28 09:48:53.66588
 2004-10-19 16:59:33.790118 | 2004-10-28 09:48:56.390719
 2004-10-19 16:59:34.398063 | 2004-10-28 09:48:56.998664

but this select statement doesnt:

SELECT create_time, create_time - '8 days 16:49:22.600601' FROM foo;

ERROR:  invalid input syntax for type timestamp: "8 days 16:49:22.600601"

How can the expression '8 days 16:49:22.600601'  be valid in the first
SELECT but not in the second?

I have reproduced this on 7.4.1 and 7.4.5.

Thanks
Luke

Re: Time intervals in select statement

From
Tom Lane
Date:
Luke McFarlane <luke@fisheye.com.au> writes:
> SELECT create_time, create_time - '8 days 16:49:22.600601' FROM foo;

> ERROR:  invalid input syntax for type timestamp: "8 days 16:49:22.600601"

Cast the constant to interval explicitly.

            regards, tom lane

Re: Time intervals in select statement

From
Luke McFarlane
Date:
Tom

Yes, that does indeed work.

It's interesting why addition assumes one operand is interval while
subtraction assumes it is timestamp? It should at least be consistent, IMHO.

Cheers
Luke

Tom Lane wrote:

>Luke McFarlane <luke@fisheye.com.au> writes:
>
>
>>SELECT create_time, create_time - '8 days 16:49:22.600601' FROM foo;
>>
>>
>
>
>
>>ERROR:  invalid input syntax for type timestamp: "8 days 16:49:22.600601"
>>
>>
>
>Cast the constant to interval explicitly.
>
>            regards, tom lane
>
>

Re: Time intervals in select statement

From
Tom Lane
Date:
Luke McFarlane <luke@fisheye.com.au> writes:
> It's interesting why addition assumes one operand is interval while
> subtraction assumes it is timestamp? It should at least be consistent, IMHO.

There is no timestamp + timestamp operator, for reasons that should be
fairly apparent.  There are both timestamp - timestamp (yielding interval)
and timestamp - interval (yielding timestamp), and in case of ambiguity
the preferred interpretation is the one with the same datatype on both
sides.  See
http://www.postgresql.org/docs/7.4/static/typeconv-oper.html

            regards, tom lane