在时间范围内分组为5分钟的时间间隔 [英] Grouping into interval of 5 minutes within a time range

问题描述

我对我想要做的mySQL命令有一些困难。

  SELECT a.timestamp，name，count（b.name）
 FROM time a，id b 
 WHERE a.user = b.user 
 AND a.id = b.id 
 AND b.name ='John'
 AND a.timestamp BETWEEN'2010-11-16 10： 30:00'AND'2010-11-16 11:00:00'
 GROUP BY a.timestamp 
  

这是我当前的输出语句。

 时间戳记名称计数（b.name）
  - ----------------- ---- ------------- 
 2010-11-16 10:32:22 John 2 
 2010-11-16 10:35:12约翰7 
 2010-11-16 10:36:34约翰1 
 2010-11-16 10:37:45约翰2 
 2010-11-16 10:48:26约翰8 
 2010-11-16 10:55:00约翰9 
 2010-11-16 10:58:08约翰2 
  

如何将它们分组为5分钟间隔结果？

我希望我的输出像

  timestamp name count（b.name）
 ------ ------------- ---- ------------- 
 2010-11-16 10:30:00约翰2 
 201 0-11-16 10:35:00约翰10 
 2010-11-16 10:40:00约翰0 
 2010-11-16 10:45:00约翰8 
 2010- 11-16 10:50:00 John 0 
 2010-11-16 10:55:00 John 11 
  

PostgreSQL

$ prefb =lang-sql prettyprint-override> SELECT
TIMESTAMP WITH TIME ZONE'epoch'+
INTERVAL'1 second'* round（extract（'epoch'from timestamp）/ 300）* 300，
as timestamp，
name，
count（b.name）
FROM time a，id
WHERE ...
GROUP BY
round（extract（'epoch'from timestamp）/ 300），name


MySQL

  SELECT 
 timestamp， - 不确定该
名称，
计数（b.name）
从时间a开始，id 
 WHERE ... 
 GROUP BY 
 UNIX_TIMESTAMP（ti mestamp）DIV 300，名称
  

I have some difficulties with mySQL commands that I want to do.

SELECT a.timestamp, name, count(b.name) 
FROM time a, id b 
WHERE a.user = b.user
  AND a.id = b.id
  AND b.name = 'John'
  AND a.timestamp BETWEEN '2010-11-16 10:30:00' AND '2010-11-16 11:00:00' 
GROUP BY a.timestamp

This is my current output statement.

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:32:22  John  2
2010-11-16 10:35:12  John  7
2010-11-16 10:36:34  John  1
2010-11-16 10:37:45  John  2
2010-11-16 10:48:26  John  8
2010-11-16 10:55:00  John  9
2010-11-16 10:58:08  John  2

How do I group them into 5 minutes interval results?

I want my output to be like

timestamp            name  count(b.name)
-------------------  ----  -------------
2010-11-16 10:30:00  John  2
2010-11-16 10:35:00  John  10
2010-11-16 10:40:00  John  0
2010-11-16 10:45:00  John  8
2010-11-16 10:50:00  John  0
2010-11-16 10:55:00  John  11 

解决方案

This works with every interval.

PostgreSQL

SELECT
    TIMESTAMP WITH TIME ZONE 'epoch' +
    INTERVAL '1 second' * round(extract('epoch' from timestamp) / 300) * 300,
    as timestamp,
    name,
    count(b.name)
FROM time a, id 
WHERE …
GROUP BY 
round(extract('epoch' from timestamp) / 300), name

MySQL

SELECT
    timestamp,  -- not sure about that
    name,
    count(b.name)
FROM time a, id 
WHERE …
GROUP BY 
UNIX_TIMESTAMP(timestamp) DIV 300, name

这篇关于在时间范围内分组为5分钟的时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！