How to Group Time-Series Data into 5-Minute Intervals Using SQL?

Aggregating Time-Series Data: 5-Minute Intervals in SQL

Analyzing time-series data often requires grouping data into specific intervals. This guide demonstrates how to group data into 5-minute intervals using SQL, addressing a scenario where data needs to be aggregated within a defined timeframe. The example uses data from 'time' and 'id' tables, counting occurrences of the name 'John'. The challenge lies in moving from individual timestamp grouping to 5-minute interval aggregation.

Solutions for Different Database Systems

The optimal approach varies depending on your database system. Here are solutions for PostgreSQL and MySQL:

PostgreSQL

PostgreSQL offers a flexible approach using extract('epoch') to get the Unix timestamp (seconds since epoch) and INTERVAL:

<code class="language-sql">SELECT
    date_trunc('minute', timestamp) + INTERVAL '5 minutes' * (extract(minute from timestamp)::int / 5) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;</code>

This query first truncates the timestamp to the minute using date_trunc. Then, it calculates the 5-minute interval by adding multiples of 5 minutes based on the minute of the original timestamp.

MySQL

MySQL provides a simpler solution using UNIX_TIMESTAMP() and integer division:

<code class="language-sql">SELECT
    FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(timestamp) / 300) * 300) AS five_minute_interval,
    name,
    COUNT(b.name)
FROM time a, id b
WHERE ... -- Your WHERE clause here
GROUP BY five_minute_interval, name
ORDER BY five_minute_interval;</code>

This query converts the timestamp to a Unix timestamp, performs integer division by 300 (seconds in 5 minutes), and then converts the result back to a timestamp using FROM_UNIXTIME().

Both queries group the results by the calculated 5-minute interval and the name, providing the desired aggregated output. Remember to replace ... with your specific WHERE clause. The ORDER BY clause ensures chronological presentation of results.

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