kamagra kart

Saturday, November 29, 2014

Find week start/end given week number

November 3, 2009 by · Leave a Comment 

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.

Top Blogs

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

*