Adding 1 to months in the future with PostgreSQL

Question

I have three columns that look something like this:

Column1       Date1         Date2
Test2         2012-06-10    
Test8         2012-05-05    2012-06-10

I'll start off by describing my desired output given this data set. It would look like this:

Year    Month    Sum
2012    05       1
2012    06       2
2012    07       1

If Column1 contains a number (which can be found by something like: WHERE Column1 LIKE '%2%'), this number should be added to the month value in Date1. This is the case with the first row, i.e. a 1 is added for month 06 and 07. However, for the case of row 2, if there is a date in Date2, it should only add 1 to Date1 and every month up to and including the month in Date2. This is why a 1 is added to 05 and 06.

I'm guessing this query would use the INTERVAL function but I'm not sure as to how I should add values to months in the future.

Update:

@CraigRinger - I'll try to explain it again. I'd like to use the number in Column1 to determine how many months into the future the date in Date1 will run. For the first row, it will run from 2012-06 to 2012-07 (as there's a 2 in Column1).

Date2 can be seen as a cancellation date. So for row two, Date1 would continue for 8 months, but as it is being cancelled (by Date2) it runs only from 05 to 06.

In other words, the query should add 1 to the beginning month (Date1) and to every month up to and including the end (or Date2 if this exists).

I'd like to add 1 to every one of these months, so that I know the sum of months for all my rows. I'm guessing this would involve adding a date interval (equal to the number in Column1) to Date1, extracting the months from the dates between these two numbers and adding a one to them. Unfortunately I have no idea how best to implement this.

Hope I've explained it better this time!

Answer 1

The syntax for adding a month to a date in postgres is:

select <date> + cast('1 months' as interval)

The rest of your logic is rather convoluted, but your question appears to be about adding months to dates.