How to group by week in mysql?

Question

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.

Answer 1

You can use both YEAR(timestamp) and WEEK(timestamp), and use both of the these expressions in the SELECT and the GROUP BY clause.

Not overly elegant, but functional...

And of course you can combine these two date parts in a single expression as well, i.e. something like

SELECT CONCAT(YEAR(timestamp), '/', WEEK(timestamp)), etc...
FROM ...
WHERE ..
GROUP BY CONCAT(YEAR(timestamp), '/', WEEK(timestamp))

Edit: As Martin xx points out you can also use the YEARWEEK() function, although its output is not as eye friendly as the longer formula above.

Answer 2

You can get the concatenated year and week number (200945) using the YEARWEEK() function. If I understand your goal correctly, that should enable you to group your multi-year data.

If you need the actual timestamp for the start of the week, it's less nice:

DATE_SUB( field, INTERVAL DAYOFWEEK( field ) - 1 DAY )

For monthly ordering, you might consider the LAST_DAY() function - sort would be by last day of the month, but that should be equivalent to sorting by first day of the month ... shouldn't it?

Answer 3

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

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

Answer 4

Just ad this in the select :

DATE_FORMAT($yourDate, \'%X %V\') as week

And

group_by(week);