CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

How to generate Month list in PostgreSQL?

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?

Upvotes: 5

Views: 7427

Answers (2)

David Furlong
David Furlong

Reputation: 1683

For people looking for an unformatted list of months:

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

Upvotes: 5

Patrick
Patrick

Reputation: 32384

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

Upvotes: 4

Related Questions