How to group by week in MySQL?

data-migration, datetime, dayofweek, group-by, mysql

Solution

Figured it out... it's a little cumbersome, but here it is.

FROM_DAYS(TO_DAYS(TIMESTAMP) -MOD(TO_DAYS(TIMESTAMP) -1, 7))

And, if your business rules say your weeks start on Mondays, change the `-1` to `-2`.

Edit

Years have gone by and I've finally gotten around to writing this up. https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

Problem

Oracle's table server offers a built-in function, `TRUNC(timestamp,'DY')`. This function converts any timestamp to midnight on the previous Sunday. What's the best way to do this in MySQL? Oracle also offers `TRUNC(timestamp,'MM')` to convert a timestamp to midnight on the first day of the month in which it occurs. In MySQL, this one is straightforward: ``` TIMESTAMP(DATE_FORMAT(timestamp, '%Y-%m-01')) ``` But this `DATE_FORMAT` trick won't work for weeks. I'm aware of the `WEEK(timestamp)` function, but I really don't want week number within the year; this stuff is for multiyear work.

Original source