Create a series of dates in PostgreSQL 9.3

Question

I am trying to generate a series of DATE objects for each day since January 1st, 2014.

The following query works:

SELECT day::DATE FROM
  (SELECT
     generate_series('2014-01-01'::DATE, now(), '1 day') as day) sq;
    day
------------
 2014-01-01
 2014-01-02
 2014-01-03
 2014-01-04
 2014-01-05
 2014-01-06
 2014-01-07
 2014-01-08
 2014-01-09
 2014-01-10
 2014-01-11
 2014-01-12
 ...
 2015-12-13
 2015-12-14
 2015-12-15
(714 rows)

However, the subquery seems inelegant to me. Is there a way to create the date objects directly from the generate_series query?

Clodoaldo Neto · Accepted Answer · 2015-12-15 13:11:35Z

1

select day::date
from generate_series('2014-01-01'::date, now(), '1 day') sq (day)

Or directly in the select list:

select generate_series('2014-01-01'::date, now(), '1 day')::date as day

answered Dec 15, 2015 at 13:11

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

1 Comment

Adam Matan Over a year ago

Spot on, amigo. Thanks!

Collectives™ on Stack Overflow

Create a series of dates in PostgreSQL 9.3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related