How to Group MySQL Data into 5-Minute Intervals?

MySQL data grouped by 5 minute intervals

This article will solve a MySQL query problem: how to group data by 5-minute intervals within a specific time range and display the summary results of each interval as a separate row.

Improved MySQL queries

This can be achieved with a simple modification of the query using PostgreSQL or MySQL syntax:

PostgreSQL:

<code class="language-sql">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</code>

MySQL:

<code class="language-sql">SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP(timestamp) DIV 300 * 300) as timestamp,
    name,
    COUNT(b.name)
FROM time a, id
WHERE …
GROUP BY 
UNIX_TIMESTAMP(timestamp) DIV 300, name</code>

Description:

The revised query contains two key changes:

1. It extracts the timestamp, rounds it to the nearest 5 minute interval (300 seconds), and converts it back to a timestamp using FROM_UNIXTIME(UNIX_TIMESTAMP(timestamp) DIV 300 * 300). (MySQL uses this method, PostgreSQL’s method is not applicable in MySQL)
2. It groups the data by rounded timestamp value (UNIX_TIMESTAMP(timestamp) DIV 300) and name, ensuring that each 5-minute interval is treated as a separate grouping.

Result:

With these changes, your query will produce the desired output, where the results are grouped by 5-minute intervals, with each interval showing the total number of occurrences of "John" in that interval.

The above is the detailed content of How to Group MySQL Data into 5-Minute Intervals?. For more information, please follow other related articles on the PHP Chinese website!