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

Question

If I have a MySQL query to aggregate totals by week, e.g.:

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

I find that the weeks appear to begin on a Sunday.

Can this be changed to a Monday?

Column dateTime is in MySQL Timestamp format 2011-09-26 12:34:32.

Answer 1

You want the sunday to belong to the previous week so remove 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

Answer 2

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