Different ways of creating intervals in PostgreSQL

Question

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?

The first one is standard SQL. The second one is PostgreSQL extension. — coladict
– coladict, Commented Oct 23, 2018 at 6:57

Laurenz Albe · Accepted Answer · 2018-10-23 22:33:02Z

2

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

answered Oct 23, 2018 at 22:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Different ways of creating intervals in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related