如何在MySQL中按周分组？ [英] How to group by week in MySQL?

问题描述

Oracle的表服务器提供了一个内置函数， TRUNC（timestamp，'DY'）。此功能将前一个星期日的任何时间戳转换为午夜。什么是在MySQL中执行此操作的最佳方法？

Oracle还提供 TRUNC（timestamp，'MM'）将时间戳转换为发生月份的第一天的午夜。在MySQL中，这个很简单：
$ b $ pre $ TIMESTAMP（DATE_FORMAT（timestamp，'％Y-％m-01'））


但是这个 DATE_FORMAT 技巧不适用于周。我知道 WEEK（timestamp）函数，但我真的不希望在一年内的周数;这个东西是为了多年的工作。

解决方案

想象一下......这有点麻烦，但在这里。 p>

  FROM_DAYS（TO_DAYS（TIMESTAMP）-MOD（TO_DAYS（TIMESTAMP）-1,7））
  -1   $ b>  $ b 
 编辑> 
 
 
 
几年过去了，我终于开始写这篇文章了。 
  http://www.plumislandmedia.net/mysql/sql - 报告时间间隔/  
 
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/ 

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