Reputation: 1852
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
Reputation: 1683
For people looking for an unformatted list of months:
select * from generate_series('2017-01-01', now(), '1 month')
Upvotes: 5
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;
Upvotes: 4