Grouping into interval of 5 minutes within a time range

by

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

More Related Contents:

  1. Using LIMIT within GROUP BY to get N results per group?
  2. Error related to only_full_group_by when executing a query in MySql
  3. MySQL Query GROUP BY day / month / year
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  6. Retrieving the last record in each group – MySQL
  7. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  8. Mysql query to dynamically convert rows to columns
  9. MySQL “Group By” and “Order By”
  10. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  11. Group mysql query by 15 min intervals
  12. In SQL, how to select the top 2 rows for each group
  13. SQL Query to get column values that correspond with MAX value of another column?
  14. MySQL Group By and Sum total value of other column
  15. Is it possible to GROUP BY multiple columns using MySQL?
  16. Using Count to find the number of occurrences
  17. What does SQL clause “GROUP BY 1” mean?
  18. Using ORDER BY and GROUP BY together
  19. MySQL: How do I join same table multiple times?
  20. GROUP BY – do not group NULL
  21. MySQL GROUP BY DateTime +/- 3 seconds
  22. MySQL Group By Hours
  23. mysql count group by having
  24. SQL query with avg and group by
  25. MySQL – Rows to Columns
  26. sql joins as venn diagram
  27. MySQL, better to insert NULL or empty string?
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. How do you force mysql LIKE to be case sensitive? [duplicate]
  30. In what order are MySQL JOINs evaluated?

Leave a Comment