Grouping into interval of 5 minutes within a time

问题:

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 

回答1:

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

回答2:

You should rather use GROUP BY UNIX_TIMESTAMP(time_stamp) DIV 300 instead of round(../300) because of the rounding I found that some records are counted into two grouped result sets.

回答3:

For postgres, I found it easier and more accurate to use the

date_trunc

function, like:

select name, sum(count), date_trunc(\'minute\',timestamp) as timestamp
FROM table
WHERE xxx
GROUP BY name,date_trunc(\'minute\',timestamp)
ORDER BY timestamp

You can provide various resolutions like \'minute\',\'hour\',\'day\' etc... to date_trunc.

回答4:

i came across the same issue.

I found that it is easy to group by any minute interval is just dividing epoch by minutes in amount of seconds and then either rounding or using floor to get ride of the remainder. So if you want to get interval in 5 minutes you would use 300 seconds.

SELECT COUNT(*) cnt, 
to_timestamp(floor((extract(\'epoch\' from timestamp_column) / 300 )) * 300) 
AT TIME ZONE \'UTC\' as interval_alias
FROM TABLE_NAME GROUP BY interval_alias

interval_alias       cnt
-------------------  ----  
2010-11-16 10:30:00  2
2010-11-16 10:35:00  10
2010-11-16 10:45:00  8
2010-11-16 10:55:00  11 

This will return the data correctly group by the selected minutes interval; however, it will not return the intervals that don\'t contains any data. In order to get those empty intervals we can use the function generate_series.

SELECT generate_series(MIN(date_trunc(\'hour\',timestamp_column)),
max(date_trunc(\'minute\',timestamp_column)),\'5m\') as interval_alias FROM 
TABLE_NAME

Result:

interval_alias       
-------------------    
2010-11-16 10:30:00  
2010-11-16 10:35:00
2010-11-16 10:40:00   
2010-11-16 10:45:00
2010-11-16 10:50:00   
2010-11-16 10:55:00   

Now to get the result with interval with zero occurrences we just outer join both result sets.

SELECT series.minute as interval,  coalesce(cnt.amnt,0) as count from 
   (
   SELECT count(*) amnt,
   to_timestamp(floor((extract(\'epoch\' from timestamp_column) / 300 )) * 300)
   AT TIME ZONE \'UTC\' as interval_alias
   from TABLE_NAME  group by interval_alias
   ) cnt

RIGHT JOIN 
   (    
   SELECT generate_series(min(date_trunc(\'hour\',timestamp_column)),
   max(date_trunc(\'minute\',timestamp_column)),\'5m\') as minute from TABLE_NAME 
   ) series

on series.minute = cnt.interval_alias

The end result will include the series with all 5 minute intervals even those that have no values.

interval             count
-------------------  ----  
2010-11-16 10:30:00  2
2010-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  0 
2010-11-16 10:55:00  11 

The interval can be easily changed by adjusting the last parameter of generate_series. In our case we use \'5m\' but it could be any interval we want.

回答5:

The query will be something like:

SELECT 
  DATE_FORMAT(
    MIN(timestamp),
    \'%d/%m/%Y %H:%i:00\'
  ) AS tmstamp,
  name,
  COUNT(id) AS cnt 
FROM
  table
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp) / 300), name

回答6:

You\'re probably going to have to break up your timestamp into ymd:HM and use DIV 5 to split the minutes up into 5-minute bins -- something like

select year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 5,
       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 year(a.timestamp), 
       month(a.timestamp), 
       hour(a.timestamp), 
       minute(a.timestamp) DIV 12

...and then futz the output in client code to appear the way you like it. Or, you can build up the whole date string using the sql concat operatorinstead of getting separate columns, if you like.

select concat(year(a.timestamp), \"-\", month(a.timestamp), \"-\" ,day(a.timestamp), 
       \" \" , lpad(hour(a.timestamp),2,\'0\'), \":\", 
       lpad((minute(a.timestamp) DIV 5) * 5, 2, \'0\'))

...and then group on that

回答7:

How about this one:

select 
    from_unixtime(unix_timestamp(timestamp) - unix_timestamp(timestamp) mod 300) as ts,  
    sum(value)
from group_interval 
group by ts 
order by ts
;

回答8:

Not sure if you still need it.

SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(timestamp))/300)*300) AS t,timestamp,count(1) as c from users GROUP BY t ORDER BY t;

2016-10-29 19:35:00 | 2016-10-29 19:35:50 | 4 |

2016-10-29 19:40:00 | 2016-10-29 19:40:37 | 5 |

2016-10-29 19:45:00 | 2016-10-29 19:45:09 | 6 |

2016-10-29 19:50:00 | 2016-10-29 19:51:14 | 4 |

2016-10-29 19:55:00 | 2016-10-29 19:56:17 | 1 |

回答9:

I found out that with MySQL probably the correct query is the following:

SELECT SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                 \'%Y-%m-%d %H:%i:%S\' ) , 1, 19 ) AS ts_CEILING,
SUM(value)
FROM group_interval
GROUP BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                   \'%Y-%m-%d %H:%i:%S\' ) , 1, 19 )
ORDER BY SUBSTRING( FROM_UNIXTIME( CEILING( timestamp /300 ) *300,  
                                   \'%Y-%m-%d %H:%i:%S\' ) , 1, 19 ) DESC

Let me know what you think.

回答10:

select 
CONCAT(CAST(CREATEDATE AS DATE),\' \',datepart(hour,createdate),\':\',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2)) AS \'5MINDATE\'
,count(something)
from TABLE
group by CONCAT(CAST(CREATEDATE AS DATE),\' \',datepart(hour,createdate),\':\',ROUNd(CAST((CAST((CAST(DATEPART(MINUTE,CREATEDATE) AS DECIMAL (18,4)))/5 AS INT)) AS DECIMAL (18,4))/12*60,2))