如何在 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/

这篇关于如何在 MySQL 中按周分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！