[SOLVED] query to find all the months between two dates

[uss_pt]

Hi,

 

i'm doing an event calendar  and i'm having some problems to solve the following problem:

 

i have a table for the events, and in that table i have the fields date_begin and date_end of the type DATE;

 

what i'm trying to get is all the months and correspondent year where a event exists.

 

Ex:

 

id  date_begin    date_end

 

1 2008-12-16 2009-01-17

2 2009-02-16 2009-02-18

3 2009-04-16 2009-05-16

4 2009-05-16 2009-07-16

 

In this case i need as a result

(12-2008,01-2009,02-2009,04-2009,05-2009,06-2009,07-2009)

 

i'm using php and mysql db

 

thx

[Ken2k7]

SELECT DATE_FORMAT(date_begin, '%m-%Y') AS list
FROM table
UNION
SELECT DATE_FORMAT(date_end, '%m-%Y') AS list
FROM table

?

 

[uss_pt]

hi...thx for your answer but still didn't solve my problem...

for the given example,with the query you said, the month/year 06-2009 it is not listed...

[kickstart]

Hi

 

I recently had to play with something to get all the dates in a date range from SQL (I was getting the info to present a graph of users posts on a forum by month and needed to cope with months where they hadn't posted anything).

 

First stage is a simple table with 1 integer column with 10 rows of 0 to 9. Column name of i, so the following SQL will get you every number from 0 to 9999:-

 

SELECT thousands.i *1000 + hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS thousands
CROSS JOIN integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units

 

If you want more possible days then just join it again against itself for the 10 thousands, etc.

 

You can then join this with the select of your dates to do date additions to the date.

 

Something like this:-

 

SELECT DATE_FORMAT( DATE_ADD( DATE_FORMAT( date_begin, '%Y-%m-01' ) , INTERVAL ournumbers
MONTH ) , '%Y-%m' ) AS ourMonths
FROM EventsTable, (

SELECT thousands.i *1000 + hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS thousands
CROSS JOIN integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units
) AS ourtable
WHERE ournumbers
BETWEEN 0 
AND Period_Diff( DATE_FORMAT( date_end, '%Y%m' ) , DATE_FORMAT( date_begin, '%Y%m' ) ) 
ORDER BY ourMonths

 

Basically it is doing a join of your table of events with the numbers 0 to 9999. Internally this should give a table of each id repeated 10000 times, once for each number from 0 to 9999. It then checks if the number is between 0 and the number of months difference between the begin and end dates (so discarding all the larger ones) and adds that number of months to the month of the begin date.

 

As it stands this will give you duplicate months (ie, with your test data May 2009 will land up output twice as it occurs in both id 4 and 5), but that should be easy for you to eliminate.

 

Hope this gives you some ideas.

 

All the best

 

Keith

[uss_pt]

hi...thk u very much ( = ...exactly this i was looking for... just added a DISTINCT in the first select so all the values are unique...

 

by the way...i would like to post the topic as solved but i dunno how to do it.

 

thx (=

[kickstart]

Hi

 

No problem.

 

Depending on the max range of the number of months of an event it could be made more efficient. Currently will cope with events up to 10000 months long (~800 years). Knock a few of the joins off if the longest event is shorter.

 

All the best

 

Keith