mysql group by weeks

Question

Query:

select  repdate, week(repdate,1) from calldrivers 
where repdate between '2013-10-01' and '2014-01-12' group by repdate

Result:

.
.
.
    2013-12-28 52 

    2013-12-29 52 

    2013-12-30 53 

    2013-12-31 53 

    2014-01-01 1 

    2014-01-02 1 

    2014-01-03 1 

    2014-01-04 1 

    2014-01-05 1 

    2014-01-06 2 

    2014-01-07 2 
.
.
.

as you can see, last week of the year has not been grouped properly. For automation purposes, i need this to be automated so the mysql can identify the week from 30 Dec 2013 till 5th of Jan as one week.

is there any solution to overcome this issue ?

EDIT **

my concern is when you use group by week(repdate,1) it will group 30th and 31st of Dec as a week and from 1st of jan till the 5th as another week. however they should be one week.

Which week is _2014-01-06_ then? 1 or 2? – Salman A Jan 13 '14 at 09:05 — Salman A, Jan 13 '14 at 09:05

score 1 · Answer 1 · edited May 23 '17 at 12:29

There's another answer that could helps you:

How to group by week in MySQL?

You could write your sql query in this manner

select  repdate, week(repdate,1) from calldrivers 
where repdate between '2013-10-01' and '2014-01-12' 
group by week(repdate,1)

This should work

Edit: Test Case

Run this test case for which mode you should run the week function The first row represents the real value for the date of the test case. After there is a couple of a query and a comment. The comment is the result for the query

-- SUNDAY, MONDAY, THURSDAY, WEDNESDAY, SUNDAY, MONDAY
SELECT WEEK('2013-12-29', 0), WEEK('2013-12-30', 0), WEEK('2013-12-31', 0), WEEK('2014-01-01', 0), WEEK('2014-01-05', 0), WEEK('2014-01-06', 0);
-- 52, 52, 52, 0, 1, 1
SELECT WEEK('2013-12-29', 1), WEEK('2013-12-30', 1), WEEK('2013-12-31', 1), WEEK('2014-01-01', 1), WEEK('2014-01-05', 1), WEEK('2014-01-06', 1);
-- 52, 53, 53, 1, 1, 2
SELECT WEEK('2013-12-29', 2), WEEK('2013-12-30', 2), WEEK('2013-12-31', 2), WEEK('2014-01-01', 2), WEEK('2014-01-05', 2), WEEK('2014-01-06', 2);
-- 52, 52, 52, 52, 1, 1
SELECT WEEK('2013-12-29', 3), WEEK('2013-12-30', 3), WEEK('2013-12-31', 3), WEEK('2014-01-01', 3), WEEK('2014-01-05', 3), WEEK('2014-01-06', 3);
-- 52, 1, 1, 1, 1, 2
SELECT WEEK('2013-12-29', 4), WEEK('2013-12-30', 4), WEEK('2013-12-31', 4), WEEK('2014-01-01', 4), WEEK('2014-01-05', 4), WEEK('2014-01-06', 4);
-- 53, 53, 53, 1, 2, 2
SELECT WEEK('2013-12-29', 5), WEEK('2013-12-30', 5), WEEK('2013-12-31', 5), WEEK('2014-01-01', 5), WEEK('2014-01-05', 5), WEEK('2014-01-06', 5);
-- 51, 52, 52, 0, 0, 1
SELECT WEEK('2013-12-29', 6), WEEK('2013-12-30', 6), WEEK('2013-12-31', 6), WEEK('2014-01-01', 6), WEEK('2014-01-05', 6), WEEK('2014-01-06', 6);
-- 1, 1, 1, 1, 2, 2
SELECT WEEK('2013-12-29', 7), WEEK('2013-12-30', 7), WEEK('2013-12-31', 7), WEEK('2014-01-01', 7), WEEK('2014-01-05', 7), WEEK('2014-01-06', 7);
-- 51, 52, 52, 52, 52, 1

I think that the mode from Monday to Sunday is the 3, because it brings the 30th and 31th of december in the first week of year. If you want group the 30th and the 31th december in one week and the 1st to 5th december in another week you must choose the mode 5.

thanks ruben for your reply . this is not my concern actually. my concern is when you use group by week(repdate,1) it will group 30th and 31st of Dec as a week and from 1st of jan till the 5th as another week. however they should be one week. — Ahmed ElGamil, Jan 13 '14 at 08:23
Try to use the WEEK(repdate, 3) instead of WEEK(repdate, 1). This could solve your problem for the 30th and 31th of december. For more information, you could read this http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week — Ruben Giaquinto, Jan 13 '14 at 08:35
This gives me the days from Monday... I don't think it's an international settings, now I edit my answer with a test case. You should run it and check if this could work — Ruben Giaquinto, Jan 13 '14 at 08:55

mysql group by weeks

1 Answers1