I have a table with some dated records. I wanted to do some weekly reports on this data. MySQL has a nifty function dubbed WEEK() which will return a week number. It allows you to break your data into week long intervals very easily. For example, the following query will tell me how many records came in each week:
SELECT COUNT(*), WEEK(mydate)
GROUP BY WEEK(mydate);
The problem here is, that the output is totally meaningless to me as I do not have a clue what does week 36 mean. What I really want is to have on the screen is a nice, human readable date interval – the beginning and ending date of any given week.
Surprisingly, this turns out the be a major pain in the ass. There is no simple function that will yield a week interval (or start/end date of a week) given a week number. You have to find these dates manually. Here is how I did it:
SELECT COUNT(*) AS reports_in_week, DATE_ADD(mydate, INTERVAL( 1-DAYOFWEEK(mydate) ) DAY), DATE_ADD(mydate, INTERVAL( 7-DAYOFWEEK(mydate) ) DAY)
GROUP BY WEEK(mydate)
How does it work? The DAYOFWEEK() function returns an integer ranging from 1 (Sunday) to 7 (Saturday). So if mydate happens to be Tuesday we get the following statements:
DATE_ADD(mydate, INTERVAL -2 DAY)
which essentially means "subtract 2 days from mydate (which is that week’s Sunday) and also:
DATE_ADD(mydate, INTERVAL 4 DAY)
which yields the date of that week’s Friday.