如何在 MySQL 中按周分组?
Oracle 的表服务器提供了一个内置函数，TRUNC(timestamp,'DY').此函数将任何时间戳转换为上一个星期日的午夜.在 MySQL 中执行此操作的最佳方法是什么?
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 还提供 TRUNC(timestamp,'MM') 将时间戳转换为它发生的月份第一天的午夜.在 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'))
但是这个 DATE_FORMAT 技巧在数周内都不起作用.我知道 WEEK(timestamp) 函数，但我真的不想要一年中的周数；这些东西适合多年工作.
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))
而且，如果您的业务规则规定每周从星期一开始，请将 -1 更改为 -2.
And, if your business rules say your weeks start on Mondays, change the -1 to -2.
编辑
多年过去了，我终于有时间写这篇文章了.https://www.plumislandmedia.net/mysql/sql-reporting-time-间隔/
Years have gone by and I've finally gotten around to writing this up. https://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/
相关文章