Postgres - Convert Date Range to Individual Month

Question

I have found similar help, but the issue was more complex, I am only good with the basics of SQL and am striking out here. I get a handful of columns a,b,c,startdate,enddate and i need to parse that data out into multiple rows depending on how many months are within the range.

Eg: a,b,c,1/1/2015, 3/15,2015 would become:

a,b,c,1/1/2015,value_here_doesnt_matter
a,b,c,2/1/2015,value_here_doesnt_matter
a,b,c,3/1/2015,value_here_doesnt_matter

Does not matter if the start date or end date is on a specific day, the only thing that matters is month and year. So if the range included any day in a given month, I'd want to output start days for each month in the range, with the 1st as a default day.

Could I have any advice on which direction to begin? I'm attempting generate_series, but am unsure if this is the right approach or how to make it work with keeping the data in the first few arbitrary columns consistent.

"I get a handful of columns a,b,c,startdate,enddate" - where do you "get" that from? Is that stored in a table somewhere? Are that parameter to a function? Variables in your application? But in general, generate_series() is the thing to use — a_horse_with_no_name

Hambone Hambone · Accepted Answer · 2016-02-05T17:04:37

I think generate_series is the way to go. Without knowing what the rest of your data looks like, I would start with something like this:

select
  a, b, c, generate_series(startdate, enddate, interval '1 month')::date
from
  my_table

Postgres - Convert Date Range to Individual Month

1 Answers