MySQL：周期日期范围从查询中的周号 [英] MySQL: week date range from week number in a query

问题描述

我有一个数据库表，看起来像这样：

I got a database table that looks something like this:

| id         | clock         | info
----------------------------------------------
| 1          | 1262556754    | some info
| 2          | 1262556230    | some other info
| 3          | 1262556988    | and another
| 4          | 1262555678    | and some more

它包含日志记录和一个unix时间戳记，当这个日志被写入时。我需要的是每周报告一次，每周有多少个日志记录。这是一个我写的查询：

It contains log records and a unix timestamp, when this log was written. What I need, is to get a weekly report, on how many log records there was during each week. Here is a query that I wrote:

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

很棒！但是我想看到的是一个日期范围，如 2010年2月08日00:00 - 2010年2月15日00:00 ，所以我的结果集将如下所示： / p>

Great! But what I would like to see, is a date ranges like 08 Feb 2010 00:00 - 15 Feb 2010 00:00, so my result set would look like this:

| 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

有没有办法做到这一点？

Is there any way to do this?

推荐答案

使用 STR_TO_DATE（'201008 Monday'，'％X％V％W'）; 获得适当的日期 2010-02-22 然后使用 DATE_FORMAT 获取所需的格式。

Use STR_TO_DATE('201008 Monday', '%X%V %W'); to get a proper date like 2010-02-22 then use DATE_FORMAT to get the format you need.

这篇关于MySQL：周期日期范围从查询中的周号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！