How to use GROUP BY (date) in MySQL to start on a specific day of the week

Question

If I have a MySQL query to summarize weekly totals, for example:

select sum(keyword1), sum(keyword2), sum(keyword3), dateTime from myTable group by week(dateTime) order by dateTime asc 

I find that weeks start on Sunday.

Can I change it to Monday?

The dateTime column is in the format Timestamp MySQL 2011-09-26 12:34:32.

Answer 1

 mysql> select week('2011-09-25', 1); +-----------------------+ | week('2011-09-25', 1) | +-----------------------+ | 38 | +-----------------------+ mysql> select week('2011-09-25', 0); +-----------------------+ | week('2011-09-25', 0) | +-----------------------+ | 39 | +-----------------------+ 

So, http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_default_week_format

Answer 2

You want Sunday to belong to the previous week, so delete one day from it

 select sum(keyword1), sum(keyword2), sum(keyword3), week(DATE_SUB(dateTime, INTERVAL 1 DAY)) my_week from myTable group by week(DATE_SUB(dateTime, INTERVAL 1 DAY)) order by my_week asc