mysql 按周分组_如何在MySQL中按周分组？

weixin_39788960

于 2021-01-18 19:00:05 发布

阅读量118

点赞数

CC 4.0 BY-SA版权

文章标签： mysql 按周分组

本文链接：https://blog.youkuaiyun.com/weixin_39788960/article/details/113122713

本文介绍了如何在MySQL中实现类似Oracle TRUNC函数的功能，即如何将时间戳截断到最近一周的周日午夜及所在月份的第一天。通过使用TO_DAYS和FROM_DAYS函数组合，可以有效地实现这一目标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.

解决方案

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.

http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/