SQL: Group Rows By Minute Intervals

When working with large datasets, especially event logs, it’s common to group data by specific time intervals to better analyze trends and patterns. In this article, we’ll walk through how to group event data into 10-minute intervals, count the occurrences of each event, and save the results in a new table. Additionally, we’ll include how to handle table creation in SQL by ensuring any pre-existing table is dropped before inserting new data.

Problem Scenario

Imagine you have a table called events that records the occurrence of various events alongside their corresponding timestamps. For example, your table might look something like this:

id time                event 
1  2024-10-08 14:05:23 login 
2  2024-10-08 14:06:45 logout 
3  2024-10-08 14:12:34 login 
4  2024-10-08 14:20:12 file_open 
5  2024-10-08 14:29:01 login

In this scenario, we want to group these events into 10-minute intervals. By doing this, we can easily count how many times each event occurred during each time period.

Step 1: Writing the SQL Query

To group event data by 10-minute intervals, we need to manipulate the time column and aggregate the event counts. Using SQLite’s strftime function, we can extract date and time components, round the minutes down to the nearest 10, and group the data accordingly.

Here’s the query to do that:

SELECT  
    strftime('%Y-%m-%d %H:', time) ||  
    substr('00' || (CAST(strftime('%M', time) AS INTEGER) / 10) * 10, -2, 2) AS ten_minute_interval, 
    event, 
    COUNT(*) AS event_count 
FROM  
    events 
GROUP BY  
    ten_minute_interval, event 
ORDER BY  
    ten_minute_interval, event;

Breakdown of the Query:

• strftime('%Y-%m-%d %H:', time): This function extracts the date and hour from the time field (formatted as YYYY-MM-DD HH).
• CAST(strftime('%M', time) AS INTEGER) / 10 * 10: This part extracts the minute value and rounds it down to the nearest multiple of 10, effectively grouping the timestamps into 10-minute intervals.
• substr('00' || ..., -2, 2): This ensures that the result for minutes is always two digits (e.g., 00, 10, 20, etc.).
• GROUP BY ten_minute_interval, event: The GROUP BY clause ensures that we count events for each 10-minute interval.
• ORDER BY ten_minute_interval, event: Sorting the output makes it easier to read and analyze.

Example Output

Running this query on the sample data above might return something like:

ten_minute_interval event     event_count 
2024-10-08 14:00    login     1 
2024-10-08 14:00    logout    1 
2024-10-08 14:10    login     1 
2024-10-08 14:20    file_open 1 
2024-10-08 14:20    login     1

Step 2: Creating a New Table with the Results

Often, it’s useful to save these grouped results in a new table for further analysis. To do this, we’ll modify the query to create a new table and insert the data into it. We’ll also make sure the new table is created from scratch by dropping any previous table that has the same name.

Here’s the SQL query to accomplish this:

-- Drop the table if it exists 
DROP TABLE IF EXISTS event_counts_by_10min; 
 
-- Create a new table and insert the query results into it 
CREATE TABLE event_counts_by_10min AS 
SELECT  
    strftime('%Y-%m-%d %H:', time) ||  
    substr('00' || (CAST(strftime('%M', time) AS INTEGER) / 10) * 10, -2, 2) AS ten_minute_interval, 
    event, 
    COUNT(*) AS event_count 
FROM  
    events 
GROUP BY  
    ten_minute_interval, event 
ORDER BY  
    ten_minute_interval, event;

Conclusion

In this article, we’ve walked through how to group event data into 10-minute intervals and count occurrences of each event within those periods. Using SQL’s strftime function, we can efficiently round timestamps and organize data for better analysis. Furthermore, we showed how to save these results into a new table using the CREATE TABLE AS statement, while ensuring any existing table is safely dropped with DROP TABLE IF EXISTS.

This approach is especially useful when working with event-driven systems, where data is often timestamped and analyzed over fixed intervals. By saving the results in a new table, we not only make the data more accessible for future queries but also enhance the performance of subsequent analyses. Whether you’re tracking login/logout activity, file access, or other event-based data, this method allows you to efficiently manage and analyze large datasets over time.