无论时区如何，都将MySQL日期时间分组为间隔-GroupingMySQLdatetimeintointervalsirrespectiveoftimezone

7  

You can use TIMESTAMPDIFF to group by intervals of time:

您可以使用TIMESTAMPDIFF按时间间隔分组:

For a specified interval of hours, you can use:

对于指定的小时间隔,您可以使用:

SELECT   '2012-08-03 00:00:00' + 
         INTERVAL FLOOR(TIMESTAMPDIFF(HOUR, '2012-08-03 00:00:00', timestamp) / ) *  HOUR AS start_time,
         COUNT(*) AS total 
FROM     event 
WHERE    timestamp >= '2012-08-03 00:00:00'
GROUP BY start_time

Replace the occurances of 2012-08-03 00:00:00 with your minimum input date.

用最小输入日期替换2012-08-03 00:00:00的出现。

is your specified interval in hours (every 2 hours, 3 hours, etc.), and you can do the same for minutes:

是您指定的小时间隔(每2小时,3小时等),您可以在几分钟内执行相同操作:

SELECT   '2012-08-03 00:00:00' + 
         INTERVAL FLOOR(TIMESTAMPDIFF(MINUTE, '2012-08-03 00:00:00', timestamp) / ) *  MINUTE AS start_time,
         COUNT(*) AS total 
FROM     event 
WHERE    timestamp >= '2012-08-03 00:00:00'
GROUP BY start_time

Where is your specified interval in minutes (every 45 minutes, 90 minutes, etc).

其中 是您指定的间隔(以分钟为单位)(每45分钟,90分钟等)。

Be sure you're passing in your minimum input date (in this example 2012-08-03 00:00:00) as the second parameter to TIMESTAMPDIFF.

确保您将最小输入日期(在此示例中为2012-08-03 00:00:00)作为TIMESTAMPDIFF的第二个参数传递。

EDIT: If you don't want to worry about which interval unit to pick in the TIMESTAMPDIFF function, then of course just do the interval by seconds (300 = 5 minutes, 3600 = 1 hour, 7200 = 2 hours, etc.)

编辑:如果您不想担心在TIMESTAMPDIFF函数中选择哪个间隔单位,那么当然只需要按秒进行间隔(300 = 5分钟,3600 = 1小时,7200 = 2小时等)

SELECT   '2012-08-03 00:00:00' + 
         INTERVAL FLOOR(TIMESTAMPDIFF(SECOND, '2012-08-03 00:00:00', timestamp) / ) *  SECOND AS start_time,
         COUNT(*) AS total 
FROM     event 
WHERE    timestamp >= '2012-08-03 00:00:00'
GROUP BY start_time

EDIT2: To address your comment pertaining to reducing the number of areas in the statement where you have to pass in your minimum parameter date, you can use:

编辑2:要解决有关减少语句中您必须通过最小参数日期的区域数量的注释,您可以使用:

SELECT   b.mindate + 
         INTERVAL FLOOR(TIMESTAMPDIFF(SECOND, b.mindate, timestamp) / ) *  SECOND AS start_time,
         COUNT(*) AS total 
FROM     event 
JOIN     (SELECT '2012-08-03 00:00:00' AS mindate) b ON timestamp >= b.mindate
GROUP BY start_time

And simply pass in your minimum datetime parameter once into the join subselect.

只需将最小日期时间参数传递到join子选择中即可。

You can even make a second column in the join subselect for your seconds interval (e.g. 3600) and name the column something like secinterval... then change the 's to b.secinterval, so you only have to pass in your minimum date parameter AND interval one time each.

您甚至可以在连接子选择中为秒间隔(例如3600)创建第二列,并将列命名为secinterval ...然后将 更改为b.secinterval,因此您只需要传入最小日期参数和间隔各一次。

SQLFiddle Demo

1  

the easier method would be:

更简单的方法是:

Method1

select date(timestamp) as date_timestamp, hour(timestamp) as hour_timestamp, count(*) as total 
from event
where timestamp>='2012-08-03 00:00:00' 
group by date_timestamp, hour_timestamp

if you would like to use your original approach.

如果你想使用你原来的方法。

Method2

select from_unixtime(floor(unix_timestamp(event.timestamp-1800)/3600)*3600+1800) as start_time, 
count(*) as total 
from event 
where timestamp>='2012-08-03 00:00:00' 
group by start_time;

for the first method, it also allows user to set different interval. For example, if user wants the log to group by 15 minutes,

对于第一种方法,它还允许用户设置不同的间隔。例如,如果用户希望日志分组15分钟,

select date(time) as date_timestamp, 
    hour(time) as hour_timestamp,  
    floor(minute(time) as minute_timestamp / 15) * 15 as minute_timestamp
    count(*) as total
from event
group by date_timestamp, hour_timestamp, minute_timestamp