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) FROM mytable 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) FROM mytable 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.
One would think that there would be a function to accomplish this automatically, but alas there is none. I think this is as simple as it gets. I hope this helps someone, because it took me quite a while to figure this out.
[tags]mysql, weekly, weekly reports, time functions, sql, databases[/tags]