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]
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)
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:
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())
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.
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.
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
Haven’t tested this cause I’m in a hurry/
[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
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!
Hehe. Wow, my MySQL-Fu is still strong. :) Glad I could help.
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().
select DATE_ADD(‘2007-09-07’,INTERVAL(2-DAYOFWEEK(‘2007-09-07’))DAY);
swipe the date with what ever date you want to pass
@Stuart – Thanks Stuart! Good tip!
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.
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.
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 ? :)
i have a question… how can i group by weeks… but starting on wednesday or tuesday?
I have a script which has the following in the beginning, can someone explains what does it mean in mysql:
@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:
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?
Thanks this was a life saver article for me, thanks a lot, really i am greatfull for sharing
Thanks a lot
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
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)’
Excellent article guys. Right on dot for what I was looking for my report this evening.
@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;
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
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
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!
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'
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
@ Senthil:
Try something like this:
That should do the trick.
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!
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!
I googled this post. Thanks ! You save me few ours :)
Hey thanks .. it’s really cool !
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)
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
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
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
OH THANK YOU SO MUCH :D GODBLESS YOU you
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…
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 :)
Thanks. Just the ticket.
Don’t call it a ‘major pain in the ass’. It’s creative SQL and good fun !
80)
@ olivier:
thank you. your query helped me solve my problem.
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)
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`)
Thanks for this, saved me loads of time in the long run.
For those of you trying to get the START of a week value beginning on Monday instead of on Sunday, I did find a slightly cleaner way in MySQL 5 to build this where the date is in a DB:
if(dayofweek(datevalue)=1,DATE(DATE_SUB(datevalue, Interval 6 DAY)), DATE(DATE_SUB(datevalue, INTERVAL(DAYOFWEEK(datevalue))-2 DAY)))
This will take the “datevalue” column from the DB, and modify it to force the previous Monday on the value.
And for the case of week starting on monday
SELECT
COUNT(*) AS reports_in_week,
DATE_ADD(date_created, INTERVAL(1-WEEKDAY(date_created)) DAY),
DATE_ADD(date_created, INTERVAL(7-WEEKDAY(date_created)) DAY)
FROM
nominations
GROUP BY
WEEK(date_created, 1)