Grouping into interval of 5 minutes within a time range

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This will help exactly what you want replace dt - your datetime c - call field astro transit1 - your table 300 refer 5 min so add 300 each time for time gap increase SELECT FROM UNIXTIME  300   ROUND  UNIX TIMESTAMP  r dt    300     AS 5datetime    SELECT r c FROM astro transit1 ra WHERE ra dt   r dt ORDER BY ra dt DESC LIMIT 1   AS first val FROM astro transit1 r GROUP BY UNIX TIMESTAMP  r dt   DIV 300 LIMIT 0   30

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] Grouping into interval of 5 minutes within a time range

Examples related to mysql

Examples related to sql

Examples related to group-by