问题描述
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 中按周分组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!