MySQL: find week start/end given week number

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]

This entry was posted in programming and tagged . Bookmark the permalink.



44 Responses to MySQL: find week start/end given week number

  1. Maxime CANADA Mozilla Firefox Windows says:

    You can use MAX and MIN to get the group by maximum and minimum values, this includes date fields.

    E.G.:
    SELECT
    COUNT(*) AS reports_in_week,
    MIN(mydate) as week_start,
    MAX(mydate) as week_end,
    FROM
    mytable
    GROUP BY
    WEEK(mydate)

    Reply  |  Quote
  2. Luke UNITED STATES Mozilla Firefox Linux says:

    Cool! It’s a good idea! Thanks!

    I played around with it, but it’s not exactly what I wanted. What I really needed is a query that will display date intervals for the weeks in which I had reports. In other words, I want to have dates of respective Sunday and Saturday for each week, not the dates of the first and last report submitted that week.

    But thanks for the tip anyway. :mrgreen:

    Reply  |  Quote
  3. Ed Davis UNITED STATES Mozilla Firefox Windows says:

    I used this to get orders placed in the same week as the day I’m asking the question…it might be put in an iterative loop to get what you were after…

    SELECT * FROM `va_orders` WHERE WEEK(`order_placed_date`) = WEEK(CURDATE())

    Reply  |  Quote
  4. Luke UNITED STATES Mozilla Firefox Windows says:

    Ed – yeah that would work. But then I’m sending n queries to the database (where n is number of iterations in the loop). It really puts a strain on the database and will be slower. You also get concurrency issues if another process is updating records as you loop…

    On the other hand a single SQL query is not only easier on the DB, but it will likely be further optimized by the database scheduler.

    Reply  |  Quote
  5. Walter UNITED STATES Mozilla Firefox Windows says:

    Thanks for this write up. It really helped me as I was looking to group data in the same way.

    One thing I caught is that if you simply do GROUP BY week(mydate) you will group together records that fall in the same week, but in different years. The data set I was working with happened to span over multiple years so this was making my result inaccurate. The solution I came up with was to create a GROUP BY value that would be unique per each year/week combination.

    SELECT
    DATE(DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)) as StartOfWeek,
    SUM(somefield)
    FROM
    sometable
    GROUP BY
    YEAR(mydate) + .01 * WEEK(mydate)

    This works in MySQL 5.0. I don’t remember off the top of my head if previous versions allowed expressions like that in the GROUP BY clause. That expression will evaluate to [year].[week] for each date. So the 14th week in 2007 would be 2007.14 while the 14th week in 2008 would be 2008.14. Seems clumsy but it worked for me.

    Reply  |  Quote
  6. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    Hmm… I think my data spanned at least 2 years. I will need to revisit this.

    Just of the top of my head – would another Group By clause fix this? As in

    GROUP BY WEEK(mydate), YEAR(mydate)

    Haven’t tested this cause I’m in a hurry/

    Reply  |  Quote
  7. Walter UNITED STATES Mozilla Firefox Windows says:

    [quote comment="5970"]Hmm… I think my data spanned at least 2 years. I will need to revisit this.

    Just of the top of my head – would another Group By clause fix this? As in

    GROUP BY WEEK(mydate), YEAR(mydate)

    Haven’t tested this cause I’m in a hurry/[/quote]

    I just tried this on my data set and it gave the same results as my initial stab at the problem. Performance was the same, but it’s easier to understand.

    Thanks!

    Reply  |  Quote
  8. Luke Maciak UNITED STATES Mozilla Firefox Windows says:

    Hehe. Wow, my MySQL-Fu is still strong. :) Glad I could help.

    Reply  |  Quote
  9. Stuart UNITED KINGDOM Mozilla Firefox Windows says:

    I’ve been looking for an answer to this problem all morning and your solution worked great. I don’t know if it has any performance benefits, but MySQL has a YEARWEEK() function which stops you having to group by WEEK() and YEAR().

    Reply  |  Quote
  10. shaun Mozilla Firefox Windows says:

    select DATE_ADD(’2007-09-07′,INTERVAL(2-DAYOFWEEK(’2007-09-07′))DAY);

    swipe the date with what ever date you want to pass

    Reply  |  Quote
  11. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux says:

    @Stuart – Thanks Stuart! Good tip!

    Reply  |  Quote
  12. Matt Wood UNITED KINGDOM Mozilla Firefox Windows says:

    This works…

    SELECT * FROM Table WHERE WEEK(‘Date’,4) = 30;

    It shows all the dates that are in the week number 30. I use mode 4 as Week 1 is the first week of the year, starting with a Sunday.

    Reply  |  Quote
  13. Hi All!

    What if I want to aggregate data for periods between Monday and Sunday? Is there any straight forward way to do that? e.g. like setting up the first weekday as Monday in the system?

    This discussion shows that Firefox is popular.

    Reply  |  Quote
  14. olivier GERMANY Mozilla Firefox Windows says:

    For iso weeks (week starting on mondays, week numbering starting at 1) you can get the first day of the week “week_id” of the year “year” using
    DATE_ADD( MAKEDATE(year,1), INTERVAL( -(((DAYOFWEEK(MAKEDATE(year,1))+1)%7)-3) + (week_id-1)*7 ) DAY)
    simple, no ? :)

    Reply  |  Quote
  15. Julio PERU Opera Windows says:

    i have a question… how can i group by weeks… but starting on wednesday or tuesday?

    Reply  |  Quote
  16. Sid UNITED STATES Internet Explorer Windows says:

    I have a script which has the following in the beginning, can someone explains what does it mean in mysql:

    SET @aStart = CURDATE() - INTERVAL 1 WEEK;  
    SET @aEnd = @aStart + INTERVAL 1 WEEK;
    SET @aStartlastweek = @aStart - INTERVAL 1 WEEK;
    SET @aEndlastweek = @aEnd - INTERVAL 1 WEEK;
    SET @aStartlast2week = @aStart - INTERVAL 2 WEEK;
    SET @aEndlast2week = @aEnd - INTERVAL 2 WEEK;
    SET @aStartlast3week = @aStart - INTERVAL 3 WEEK;
    SET @aEndlast3week = @aEnd - INTERVAL 3 WEEK;
    SET @aStartlast4week = @aStart - INTERVAL 4 WEEK;
    SET @aEndlast4week = @aEnd - INTERVAL 4 WEEK;
     
     
    SET @bStartNorm = 5;
    SET @bEndNorm = 23;
    SET @bRspCode = 999;
    SET @bMaxAvg  = 120;
    Reply  |  Quote
  17. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux Terminalist says:

    @Sid: What do you mean? I think it is sort of self explanatory.

    @aStart will be the date exactly a week before today
    @aEnd will be a week from @aStart – which is today
    @aStartlastweek will be a date 1 week prior to @aStart

    And so on. When it says:

    SET @foo = @bar - INTERVAL n WEEK

    it just means to count back n weeks from the date in @bar and put the result in @foo. The last 4 lines are just assignments.

    Does that make sense?

    Reply  |  Quote
  18. vijay INDIA Mozilla Firefox Windows says:

    Thanks this was a life saver article for me, thanks a lot, really i am greatfull for sharing

    Reply  |  Quote
  19. kash INDIA Mozilla Firefox Windows says:

    Thanks a lot

    Reply  |  Quote
  20. Rob UNITED KINGDOM Internet Explorer Windows says:

    Hi Guys,

    This query works great for week ending Saturday, but how do I define week ending as ‘Sunday’? Can I set Monday as ’1′ and Sunday as ’7′ and then apply the same select statement as above?

    Cheers,
    Rob

    Reply  |  Quote
  21. Rob UNITED KINGDOM Internet Explorer Windows says:

    p.s. I tried the statement suggested above, but it says ‘MAKEDATE’ is not a recognised MySQL function.

    ‘DATE_ADD( MAKEDATE(year,1), INTERVAL( -(((DAYOFWEEK(MAKEDATE(year,1))+1)%7)-3) + (week_id-1)*7 ) DAY)’

    Reply  |  Quote
  22. bingo UNITED STATES Internet Explorer Windows says:

    Excellent article guys. Right on dot for what I was looking for my report this evening.

    Reply  |  Quote
  23. bingo UNITED STATES Internet Explorer Windows says:

    @Snehapadma SaaS:
    Try this – I am using YearWeek to combine the last week at the end of year with first week in the begining of next year. Also mode 1 in YearWeek function means that week start from Monday to Sunday. For start date/end date functions simply add 1 to Interval in Date_Add as shown below. Make sense??
    SELECT count(*), DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate))+1 DAY) as Week_Start_Date, DATE_ADD(mydate, INTERVAL(7-DAYOFWEEK(mydate))+1 DAY) as week_end_date, YearWeek(mydate,1) as YearAndWeek
    FROM mytable
    group by YearAndWeek;

    Reply  |  Quote
  24. Rob UNITED KINGDOM Internet Explorer Windows says:

    Hey Bingo,

    That’s brilliant, it works perfectly; I also feel a bit silly now ‘+1 DAY’! The simplest solutions are always staring you in the face!!

    Cheers

    Reply  |  Quote
  25. Elemental Mozilla Firefox Windows says:

    Thanks very much, it helped us with using the MYSQL query to get the start and end days of a week in our PHP application.

    Elemental

    Reply  |  Quote
  26. Rob UNITED KINGDOM Google Chrome Windows says:

    Hi Bingo,

    I’ve been looking into the query a bit more and I’ve realised it doesn’t work for Sundays. For example, if I use the ‘week_end_date’ query for a day with date 09/08/09, which should be the end of the week, it returns the 16/08/09 as the end of the week.

    To get over this problem I’ve used an ‘IF’ statement, which makes the query work properly for weeks ending on Sunday;

    IF(`mydate` < DATE(DATE_ADD(`mydate`, INTERVAL(1-DAYOFWEEK(`mydate`))+1 DAY)),
    DATE(DATE_ADD(`mydate`, INTERVAL(7-DAYOFWEEK(`mydate`))-6 DAY)),
    DATE(DATE_ADD(`mydate`, INTERVAL(7-DAYOFWEEK(`mydate`))+1 DAY))) AS 'week_end_date'

    Try it out; it works a treat!

    Reply  |  Quote
  27. Rob UNITED KINGDOM Google Chrome Windows says:

    Hi Guys (posting this again, as it didn’t seem to post correctly),

    I’ve been looking at this query some more and I realised it doesn’t work for the Sunday of week ending Sunday. For example, the date 09/08/09 should be week ending 09/08/09, but using the ‘week_end_date’ in Bingos reply, returns the 16/08/09.

    To rectify this I’ve expanded the query to use an ‘IF’ statement and it works a treat! Try it out;

    IF(`mydate` < DATE(DATE_ADD(`mydate`, INTERVAL(1-DAYOFWEEK(`mydate`))+1 DAY)),
    DATE(DATE_ADD(`mydate`, INTERVAL(7-DAYOFWEEK(`mydate`))-6 DAY)),
    DATE(DATE_ADD(`mydate`, INTERVAL(7-DAYOFWEEK(`mydate`))+1 DAY))) AS 'week_end_date'

    Reply  |  Quote
  28. Senthil INDIA Mozilla Firefox Fedora Linux says:

    Hey I am new to here, well i am trying to fetch the record from the table using mysql, actually my question is “how to fetch the record from anytable with using date column should not fetch the ‘Sundays’ records ” so i would expect the data from the table as should not need to considered the sunday in week days, i hope reply will be appear soon

    Reply  |  Quote
  29. Luke Maciak UNITED STATES Mozilla Firefox Ubuntu Linux Terminalist says:

    @ Senthil:

    Try something like this:

    SELECT * FROM some_table 
    WHERE DAYNAME(date_column) <> 'Sunday'

    That should do the trick.

    Reply  |  Quote
  30. Jonathan UNITED STATES Google Chrome Windows says:

    Thanks for this article. It’s opened my eyes to some more of MySQL that will clean and speed my code. I had been using the calling script to manually collate individual dates into the days of week. Now things are much prettier!

    Reply  |  Quote
  31. pThomas UNITED STATES Mozilla Firefox Windows says:

    Very good post. And your right, it would have been a pain to figure that out on my own (if possible at all). Your example worked for my weekly reporting query needs and your explanation was great.

    Thanks!

    Reply  |  Quote
  32. Marek POLAND Internet Explorer Windows says:

    I googled this post. Thanks ! You save me few ours :)

    Reply  |  Quote
  33. gowri INDIA Google Chrome Windows says:

    Hey thanks .. it’s really cool !

    Reply  |  Quote
  34. Caroline AUSTRALIA Mozilla Firefox Windows says:

    hi all, here is the solution for weeks beginning mondays instead.
    - using YEARWEEK allows you to cover weeks across different years, has a mode for weeks beginning on monday instead of sunday
    - also using WEEKDAY (0 = Monday, 1 = Tuesday, … 6 = Sunday) instead of DAYOFWEEK (1 = Sunday, 2 = Monday, …, 7 = Saturday)

    SELECT
    COUNT(*) AS reports_in_week,
    DATE_ADD(mydate, INTERVAL(0-WEEKDAY(mydate)) DAY),
    DATE_ADD(mydate, INTERVAL(6-WEEKDAY(mydate)) DAY)
    FROM
    mytable
    GROUP BY
    YEARWEEK(mydate,1)

    Reply  |  Quote
  35. Avi INDIA Google Chrome Windows says:

    Dear All,

    This post is really great! .. but can anyone help me in my query..

    i want the count of week according to financial year like in my country its first of april (2011-04-01) . so i was trying WEEK(DATE) function of mysql but it starts according to the current year. Is there any means by which we can just say tell WEEK() function that year start with particular date like in my case ’2011-04-01′.

    Thanks & Regards,
    Avi

    Reply  |  Quote
  36. Rob UNITED KINGDOM Google Chrome Windows says:

    Hey Avi,

    Do you mean you want 01-04-2011 to be displayed as week 1 and then show incremental week numbers from that point forward?

    Thanks,
    Rob

    Reply  |  Quote
  37. Avi INDIA Google Chrome Windows says:

    Hi Rob,

    Sorry for late reply.

    Yes , but there is 1 more condition , week should starts from Monday if you check in the calendar 2011 u will find that ’2011-04-01′ starts with Friday, so 1,2,3 april should be First week (should show value = 1) then afterwards should increase accordingly.

    Thanks & Regards,
    Avi

    Reply  |  Quote
  38. leaan PHILIPPINES Google Chrome Windows says:

    OH THANK YOU SO MUCH :D GODBLESS YOU you

    Reply  |  Quote
  39. Sunny PAKISTAN Google Chrome Windows says:

    Can anybody help in writing the script(query).
    I want to display the records in a way that page 1 contains the records of current week, page 2 contains the records of last week, page 3 contains the records of before last week, and so on….. Where Page # is the paging number…

    Plz help in writing the query. I am using PHP/MySQL…

    Reply  |  Quote
  40. Andy UNITED KINGDOM Opera Windows says:

    Just wanted to add my two cents. I needed a query that would fetch me the date of Monday of a week of the specified date. I.e. if I have some date which is on Wednesday, I needed to get the date of the Monday on the same week.

    The query is as follows:

    SELECT DATE_SUB('2012-02-05', INTERVAL ((DAYOFWEEK('2012-02-05') + 12) % 7) DAY)

    Obviously, you replace the date with your date field but this works :)

    Reply  |  Quote
  41. Anthony UNITED KINGDOM Google Chrome Linux says:

    Thanks. Just the ticket.

    Don’t call it a ‘major pain in the ass’. It’s creative SQL and good fun !

    80)

    Reply  |  Quote
  42. Siva UNITED STATES Google Chrome Windows says:

    @ olivier:
    thank you. your query helped me solve my problem.

    Reply  |  Quote
  43. Matija Baric Mozilla Firefox Ubuntu Linux says:

    Hi all, greetings.
    Thank you Luke and others for the replies and discusson.
    I made small tweaks reading discussions above so I share also here.
    Thank you all, amazing and helpfull article.


    SELECT
    DATE_ADD(msci_date, INTERVAL(2-DAYOFWEEK(msci_date)) DAY) week_start,
    DATE_ADD(msci_date, INTERVAL(8-DAYOFWEEK(msci_date)) DAY) week_end
    FROM
    master_schedule_csv_import
    GROUP BY
    YEAR(msci_date) + .01 * WEEK(msci_date)

    Reply  |  Quote
  44. Matija Baric Mozilla Firefox Ubuntu Linux says:

    Earlier code will have duplicate rows for the end of the year, for example

    2013-12-30 – 2014-01-05
    2013-12-30 – 2014-01-05

    so to handle it put DISTINCT after SELECT, this query should do the trick:


    SELECT DISTINCT
    DATE_ADD(msci_date, INTERVAL(2-DAYOFWEEK(msci_date)) DAY),
    DATE_ADD(msci_date, INTERVAL(8-DAYOFWEEK(msci_date)) DAY)
    FROM
    `master_schedule_csv_import`
    GROUP BY
    YEAR(msci_date),WEEK(`msci_date`)

    Reply  |  Quote

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>