Terminally Incoherent

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

This entry was posted on Friday, September 22nd, 2006 at 4:39 pm and is filed under technology, programming, mysql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Related Posts:

MySQL Performance Tips

Dump MySQL table into CSV file

Monster Weekend (but not for me)

MySQL Admin 1.0.19: libmysqlx.dll Error

DST Readines Test for MySQL and PHP

Dreamhost Outage

MySQL no Longer Provides Free Binaries

MySQL: How to get the key of last inerted row in PHP

MINUS query in MySQL

Project Idea

17 Responses to “MySQL: find week start/end given week number”

Maxime Says:
October 27th, 2006 at 10:08 am (1421) [Quote]
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)
Posted using Mozilla Firefox 2.0 on Windows XP
Luke Says:
October 27th, 2006 at 10:27 am (1424) [Quote]
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.
Posted using Mozilla Firefox 2.0 on Linux
Ed Davis Says:
January 23rd, 2007 at 7:57 pm (2546) [Quote]
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())
Posted using Mozilla Firefox 1.5.0.9 on Windows XP
Luke Says:
January 23rd, 2007 at 8:10 pm (2547) [Quote]
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.
Posted using Mozilla Firefox 2.0.0.1 on Windows XP
Walter Says:
August 29th, 2007 at 2:40 pm (5967) [Quote]
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.
Posted using Mozilla Firefox 2.0.0.6 on Windows Vista
Luke Maciak Says:
August 29th, 2007 at 5:38 pm (5970) [Quote]
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/
Posted using Mozilla Firefox 2.0.0.6 on Ubuntu Linux
Walter Says:
August 30th, 2007 at 7:14 pm (5986) [Quote]
Luke Maciak said:
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/
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!
Posted using Mozilla Firefox 2.0.0.6 on Windows Vista
Luke Maciak Says:
August 30th, 2007 at 7:45 pm (5987) [Quote]
Hehe. Wow, my MySQL-Fu is still strong. Glad I could help.
Posted using Mozilla Firefox 2.0.0.6 on Windows XP
Stuart Says:
September 6th, 2007 at 5:53 am (6076) [Quote]
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().
Posted using Mozilla Firefox 2.0.0.6 on Windows XP
shaun Says:
September 6th, 2007 at 8:28 am (6077) [Quote]
select DATE_ADD(’2007-09-07′,INTERVAL(2-DAYOFWEEK(’2007-09-07′))DAY);

swipe the date with what ever date you want to pass
Posted using Mozilla Firefox 2.0.0.6 on Windows XP
Luke Maciak Says:
September 6th, 2007 at 10:33 am (6080) [Quote]
@Stuart - Thanks Stuart! Good tip!
Posted using Mozilla Firefox 2.0.0.6 on Ubuntu Linux
Matt Wood Says:
October 16th, 2007 at 9:34 am (6576) [Quote]
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.
Posted using Mozilla Firefox 2.0.0.7 on Windows XP
Snehapadma SaaS Says:
December 29th, 2007 at 11:10 pm (7536) [Quote]
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.
Posted using Mozilla Firefox 2.0.0.11 on Windows XP
olivier Says:
March 5th, 2008 at 4:11 pm (8341) [Quote]
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 ?
Posted using Mozilla Firefox 2.0.0.12 on Windows XP
Julio Says:
July 31st, 2008 at 7:52 pm (9761) [Quote]
i have a question… how can i group by weeks… but starting on wednesday or tuesday?
Posted using Opera 9.50 on Windows 2000

Sid

Says:

September 23rd, 2008 at 1:28 pm (10226) [Quote]

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;

Posted using

Internet Explorer 6.0 on

Windows XP

Luke Maciak Says:
September 23rd, 2008 at 1:47 pm (10227) [Quote]
@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?
Posted using Mozilla Firefox 3.0.1 on Ubuntu Linux

MySQL: find week start/end given week number

17 Responses to “MySQL: find week start/end given week number”

Leave a Reply

Status

Pages

Archives

Categories