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:
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:
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:
which essentially means “subtract 2 days from mydate (which is that week’s Sunday) and also:
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.
More than 3 requests, I'll translate this to Chinese.
超过3个请求,我就会把这篇文章翻译成中文。
No comments:
Post a Comment