我有一个类似这样的数据库表:
| id | clock | info
----------------------------------------------
| 1 | 1262556754 | some info
| 2 | 1262556230 | some other info
| 3 | 1262556988 | and another
| 4 | 1262555678 | and some more
它包含日志记录和一个Unix时间戳。我需要的是得到一份周报,关于每周有多少日志记录。下面是我写的一个查询:
SELECT
DATE_FORMAT(FROM_UNIXTIME(clock), "%U") AS week
count(*) as cnt
FROM logs
WHERE DATE_FORMAT(FROM_UNIXTIME(clock), "%Y") = '2010'
GROUP BY week
结果如下:
| week | cnt
-------------------------------
| 1 | 55
| 2 | 134
| 4 | 765
| 20 | 65
伟大的!但是我想看到的是日期范围
08 Feb 2010 00:00 - 15 Feb 2010 00:00
,所以我的结果集如下所示:
| day_start | day_end | cnt
---------------------------------------------------------
| 08 Feb 2010 00:00 | 15 Feb 2010 00:00 | 55
| 15 Feb 2010 00:00 | 22 Feb 2010 00:00 | 76
| 22 Feb 2010 00:00 | 01 Mar 2010 00:00 | 756
有什么办法吗?