Group MySQL query by 15 min intervals

UNIX_TIMESTAMP for crisp intervals

If you're fond of UNIX time stamps, try this method:

/* It's UNIX time baby! */
SELECT COUNT(*) AS count,
        FROM_UNIXTIME((FLOOR(UNIX_TIMESTAMP(created_at)/900)*900)) AS time_interval
FROM events
GROUP BY time_interval;


Here, we convert to UNIX time, apply FLOOR to group of 15 minutes span (900 seconds), and then use FROM_UNIXTIME to convert back to a more human-readable date-time format.

Crafting custom intervals

Custom interval in a jiffy, here's how:

/* Flex those time muscles */
SELECT ...
       SEC_TO_TIME(TIME_TO_SEC(created_at) - MOD(TIME_TO_SEC(created_at), 900)) AS time_interval,
       ...


This makes use of SEC_TO_TIME and TIME_TO_SEC functions for conversions and modulus operation for consistent slicing of time.

Perfecting precision with ROUND

In scenarios when FLOOR just won't do, the ROUND function comes to the rescue.

/* ROUND we go */
SELECT ...
       ROUND(UNIX_TIMESTAMP(created_at)/(15 * 60)) AS time_rounded,
       ...


This rounds to the nearest 15-minute mark. It's more forgiving, but be wary of round-off errors.

Reckoning with data types

Juggling data types? Here you go.

/* Keep calm and CONVERT */
SELECT ...
       CONVERT(time_interval, DATETIME) AS converted_interval,
       ...


CONVERT expresses your data in different types. Be mindful this could take a toll on performance with large datasets.

Careful extraction with SUBSTRING

For extracting specific parts of the timestamp, use SUBSTRING:

/* A SUBSTRING in time... */
SELECT ...
       SUBSTRING(created_at, 1, 16) - 
       INTERVAL (MINUTE(created_at) % 15) MINUTE AS neat_interval,
       ...


Test before you rest

Always test for grouping accuracy.

/* Trust, but verify */
SELECT ...
       /* your interval calculation here */,
       ...
WHERE created_at BETWEEN '2023-01-01' AND '2023-01-02'


A sample timeframe can help verify if your grouping logic holds up.

Performance and precision

Performance implications are critical with time interval grouping.

/* Performance is not a sprint, it's a marathon */
SELECT /* use proper indexing */,
       /* consider caching strategies */,
       ...


Assess the collision between precision and performance, especially when dealing with high-frequency data.

Learn and adapt

Keep exploring the MySQL documentation and community discussions for optimized methodologies and advanced time functions.

Curate your data

View each 15-minute interval as a selective exhibition of your data points. Craft your queries to effectively categorize these exhibition pieces.