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





---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Luke McFarlane <luke (AT) fisheye (DOT) com.au> writes:
Cast the constant to interval explicitly.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

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:


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Luke McFarlane <luke (AT) fisheye (DOT) com.au> writes:
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/s...conv-oper.html

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html