How to generate Month list in PostgreSQL?

Question

I have a table A with startdate column which is TIMESTAMP WITHOUT TIME ZONE I need to write a query/function that generate a list of months from the MIN value of the column till MAX value of the column.

For example:

startdate
2014-12-08
2015-06-16
2015-02-17

will generate a list of: (Dec-14,Jan-15,Feb-15,Mar-15,Apr-15,May-15,Jun-15)

How do I do that? I never used PostgreSQL to generate data that wasn't there... it always has been finding the correct data in the DB... any ideas how to do that? Is it doable in a query?

Use generate_series(): postgresql.org/docs/current/interactive/functions-srf.html — Frank Heikens
– Frank Heikens, Commented Jul 16, 2015 at 8:13

David Furlong · Accepted Answer · 2019-04-08 12:32:38Z

5

For people looking for an unformatted list of months:

select * from generate_series('2017-01-01', now(), '1 month')

answered Apr 8, 2019 at 12:32

David Furlong

1,7031 gold badge14 silver badges17 bronze badges

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

Comments

Patrick · Accepted Answer · 2015-07-16 08:57:10Z

4

You can generate sequences of data with the generate_series() function:

SELECT to_char(generate_series(min, max, '1 month'), 'Mon-YY') AS "Mon-YY"
FROM (
  SELECT date_trunc('month', min(startdate)) AS min, 
         date_trunc('month', max(startdate)) AS max
  FROM a) sub;

This generates a row for every month, in a pretty format. If you want to have it like a list, you can aggregate them all in an outer query:

SELECT string_agg("Mon-YY", ', ') AS "Mon-YY list"
FROM (
  -- Query above
) subsub;

SQLFiddle here

edited Jul 16, 2015 at 8:57

answered Jul 16, 2015 at 8:47

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

How to generate Month list in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related