CODEWITHSUNDEEP
postgresqlintervals
Eugene Yarmash
Eugene Yarmash

Reputation: 150128

Different ways of creating intervals in PostgreSQL

I've recently come across this way of using the interval type in PostgreSQL:

SELECT CURRENT_DATE + INTERVAL '1' month;
      ?column?       
---------------------
 2018-11-22 00:00:00

From the docs, it looks like the “proper” way to do it would be:

SELECT CURRENT_DATE + INTERVAL '1 month';
      ?column?       
---------------------
 2018-11-22 00:00:00

Are the two queries equivalent? Where's the former syntax documented?

Upvotes: 3

Views: 981

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247940

The first syntax is SQL standard syntax:

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

<interval qualifier> ::=
    <start field> TO <end field>
  | <single datetime field>

I'll spare you the definition of <start field>, <end field> and <single datetime field>, but essentially they can take the values YEAR, MONTH, DAY, MINUTE or SECOND.

PostgreSQL doesn't support the <sign>, you'd have to add that to the string.

Here a few samples:

INTERVAL '1-10' YEAR TO SECOND
INTERVAL '20:03:15' DAY TO SECOND
INTERVAL '1' DAY

Upvotes: 2

Related Questions