4

Till now I used the following syntax to add an interval to a timestamp:

select now() + '5 year';

This worked fine till I tried to subtract the interval which results in a syntax error.

invalid input syntax for type timestamp with time zone: "5 year"
LINE 1: select now() - '5 year'

In the documentation I learned that the syntax actually is:

select now() - interval '5 year'

So my questions are: Why does select now() + '5 year' work at all? Does it work only by accident and it might break in a future Postgresql release?

  • I am not sure if this question is on-topic as "Basic SQL" questions are off-topic. But I am not sure if this is still "Basic SQL"... – gillesB Jan 23 at 14:07
  • 2
    100 % OT, let me assure you. Not as trivial as it may seem. – Erwin Brandstetter Jan 23 at 19:10
  • @ErwinBrandstetter does "OT" stand for "On Topic" or "Off Topic"? – Brad Jan 23 at 23:13
  • @Brad: Sorry for the ambiguity. On Topic. – Erwin Brandstetter Jan 23 at 23:15
3

Like @a_horse explained, there are two operators available for the expression now() - '5 year':

SELECT oprleft::regtype, oprname, oprright::regtype
FROM   pg_operator
WHERE  oprname = '-'
AND    oprleft = 'timestamptz'::regtype;

         oprleft          | oprname |         oprright         
--------------------------+---------+--------------------------
 timestamp with time zone | -       | timestamp with time zone
 timestamp with time zone | -       | interval
(2 rows)

The exact reason for the choice can be found in the manual in the chapter Operator Type Resolution:

[...]

 2. Check for an operator accepting exactly the input argument types. If one exists (there can be only one exact match in the set of operators considered), use it. [...]

    a. If one argument of a binary operator invocation is of the unknown type, then assume it is the same type as the other argument for this check. [...]

[...]

Bold emphasis mine. Read the whole chapter to understand the process fully.

The same type is preferred if one argument type is unknown and a matching operator is available. There is an operator for timestamptz - timestamptz, bingo. The operator is resolved here. Fortunately, '5 years' is illegal input for timestamptz, else this might result in confusion!

The operator resolves to timestamptz - interval after adding an explicit type cast:

now() - interval '5 year'  -- always the way to go
4

My guess(!) is:

The + operator for timestamps only supports adding an interval (timestamp + interval). And thus it's clear that the string value '5 year' needs to be (implicitly) converted to an interval

The - operator on the other hand supports two different combinations:

  • timestamp - timestamp
  • timestamp - interval.

Apparently Postgres prefers to use the timestamp - timestamp option and tries to (implicitly) convert '5 year' to a timestamp which of course fails.

  • I thought this was due to the fact that timestamptz (not timestamp) is the preferred type among "Date/time types" in Postgres - and commented as much. But on a second look, there seems to be a different explanation. And interval is not a "Date/time type", but a "Timespan type" in the Postgres type system. (Your answer is still basically right.) – Erwin Brandstetter Jan 23 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.