Skip to main content

MySQL - Group Data By 5, 10, 15, 20, or 30 Minute Intervals

When aggregating data for high traffic split tests, the business stakeholders often ask data analysts to group and report data by 15 minute intervals. It is not detailed enough to group data hourly as high volume split tests need to be monitored closely for any critical issue that might have significant negative impact on sales or other metrics.

In today's post, we'll walk through a few SQL snippets to round Date/Time records to the 15 minute increment and group them by 15 minute intervals.

If you are using Microsoft SQL Server database engine, you can visit SQL Server - Group Data By 5, 10, 15, 20, or 30 Minute Intervals to check out T-SQL scripts.

Example Data​

create table login_logs (
	account_id INT,
	login_datetime DATETIME
);
insert into login_logs (account_id, login_datetime) values (1, '2022-03-02 02:05:02');
insert into login_logs (account_id, login_datetime) values (2, '2022-03-02 02:09:07');
insert into login_logs (account_id, login_datetime) values (3, '2022-03-02 02:14:07');
insert into login_logs (account_id, login_datetime) values (4, '2022-03-02 02:16:07');
insert into login_logs (account_id, login_datetime) values (5, '2022-03-02 02:27:07');
insert into login_logs (account_id, login_datetime) values (6, '2022-03-02 02:28:07');
insert into login_logs (account_id, login_datetime) values (7, '2022-03-02 02:30:00');
insert into login_logs (account_id, login_datetime) values (8, '2022-03-02 02:36:02');
insert into login_logs (account_id, login_datetime) values (9, '2022-03-02 02:40:07');
insert into login_logs (account_id, login_datetime) values (10, '2022-03-02 02:46:17');
insert into login_logs (account_id, login_datetime) values (11, '2022-03-02 02:54:27');
insert into login_logs (account_id, login_datetime) values (12, '2022-03-02 02:58:10');

Use

DB-Fiddle to execute SQL scripts on sample data.

Group By 15 Minute Interval - Round Up Date/Time​

If the business would like to round up each login_datetime value to 15 minute interval, we can apply the following logics.

  1. Use TIMESTAMPDIFF function to calculate the minute difference between the login_datetime and MySQL begin_datetime '1000-01-01 00:00:00'
  2. Divide the calculated minute difference by 15
  3. Invoke CEILING function to return the smallest integer value that is greater than or equal to the division result
  4. Multiply the ceiling result by 15 to get the round up minute difference value
  5. Finally, add the round up minute difference value to MySQL begin_datetime to get the new round up login_datetime values in 15 minute intervals
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT account_id,
	login_datetime,
	TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
	CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins AS diff_round_up,
	DATE_ADD(@begin_datetime, Interval CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute) AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
account_idlogin_datetimediffdiff_round_uplogin_datetime_15_min_interval
12022-03-02 02:05:025376068455376068552022-03-02 02:15:00
22022-03-02 02:09:075376068495376068552022-03-02 02:15:00
32022-03-02 02:14:075376068545376068552022-03-02 02:15:00
42022-03-02 02:16:075376068565376068702022-03-02 02:30:00
52022-03-02 02:27:075376068675376068702022-03-02 02:30:00
62022-03-02 02:28:075376068685376068702022-03-02 02:30:00
72022-03-02 02:30:005376068705376068702022-03-02 02:30:00
82022-03-02 02:36:025376068765376068852022-03-02 02:45:00
92022-03-02 02:40:075376068805376068852022-03-02 02:45:00
102022-03-02 02:46:175376068865376069002022-03-02 03:00:00
112022-03-02 02:54:275376068945376069002022-03-02 03:00:00
122022-03-02 02:58:105376068985376069002022-03-02 03:00:00

Now you can group accout login data by round up 15 minute intervals.

SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT DATE_ADD(@begin_datetime, Interval CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute) AS login_datetime_15_min_interval,
	COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins
2022-03-02 02:15:003
2022-03-02 02:30:004
2022-03-02 02:45:002
2022-03-02 03:00:003

Group By 15 Minute Interval - Round Down Date/Time​

If the business would like to round down each login_datetime value to 15 minute interval, we can apply the same logics described above and simply substitute CEILING with FLOOR function.

SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT account_id,
	login_datetime,
	TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
	FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins AS diff_round_down,
	DATE_ADD(@begin_datetime, Interval FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute) AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
account_idlogin_datetimediffdiff_round_downlogin_datetime_15_min_interval
12022-03-02 02:05:025376068455376068402022-03-02 02:00:00
22022-03-02 02:09:075376068495376068402022-03-02 02:00:00
32022-03-02 02:14:075376068545376068402022-03-02 02:00:00
42022-03-02 02:16:075376068565376068552022-03-02 02:15:00
52022-03-02 02:27:075376068675376068552022-03-02 02:15:00
62022-03-02 02:28:075376068685376068552022-03-02 02:15:00
72022-03-02 02:30:005376068705376068702022-03-02 02:30:00
82022-03-02 02:36:025376068765376068702022-03-02 02:30:00
92022-03-02 02:40:075376068805376068702022-03-02 02:30:00
102022-03-02 02:46:175376068865376068852022-03-02 02:45:00
112022-03-02 02:54:275376068945376068852022-03-02 02:45:00
122022-03-02 02:58:105376068985376068852022-03-02 02:45:00

Now you can group accout login data by round down 15 minute intervals.

SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT DATE_ADD(@begin_datetime, Interval FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute) AS login_datetime_15_min_interval,
	COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins
2022-03-02 02:00:003
2022-03-02 02:15:003
2022-03-02 02:30:003
2022-03-02 02:45:003

Group By 15 Minute Interval - Round To Nearest​

If the business would like to round each login_date value to its nearest 15 minute interval, we could use the following logics to achieve it.

  1. Use TIMESTAMPDIFF function to calculate the minute difference between the login_datetime and MySQL begin_datetime '1000-01-01 00:00:00'
  2. Divide the calculated minute difference by 15
  3. Invoke MOD function to return returns the remainder of the above step result divided by 1. This would give us a number between 0 and 1 which we can use us a nearest indicator
  4. If the nearest_indicator value is greater or equal to 0.5, we would apply CEILING' function, otherwise apply FLOOR' function to get the nearest 15 minute interval
SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT account_id,
	login_datetime,
	MOD(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins, 1) AS nearest_indicator,
	CASE
		WHEN MOD(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins, 1) >= 0.5
			THEN DATE_ADD(@begin_datetime, Interval CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		ELSE DATE_ADD(@begin_datetime, Interval FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		END AS login_datetime_15_min_interval
FROM login_logs
ORDER BY login_datetime;
account_idlogin_datetimenearest_indicatorlogin_datetime_15_min_interval
12022-03-02 02:05:020.33332022-03-02 02:00:00
22022-03-02 02:09:070.60002022-03-02 02:15:00
32022-03-02 02:14:070.93332022-03-02 02:15:00
42022-03-02 02:16:070.06672022-03-02 02:15:00
52022-03-02 02:27:070.80002022-03-02 02:30:00
62022-03-02 02:28:070.86672022-03-02 02:30:00
72022-03-02 02:30:000.00002022-03-02 02:30:00
82022-03-02 02:36:020.40002022-03-02 02:30:00
92022-03-02 02:40:070.66672022-03-02 02:45:00
102022-03-02 02:46:170.06672022-03-02 02:45:00
112022-03-02 02:54:270.60002022-03-02 03:00:00
122022-03-02 02:58:100.86672022-03-02 03:00:00

Now you can group accout login data by round to nearest 15 minute intervals.

SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 15;

SELECT CASE
		WHEN MOD(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins, 1) >= 0.5
			THEN DATE_ADD(@begin_datetime, Interval CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		ELSE DATE_ADD(@begin_datetime, Interval FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		END AS login_datetime_15_min_interval,
	COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_15_min_interval
ORDER BY login_datetime_15_min_interval;
login_datetime_15_min_intervaldistinct_account_logins
2022-03-02 02:00:001
2022-03-02 02:15:003
2022-03-02 02:30:004
2022-03-02 02:45:002
2022-03-02 03:00:002

Group By 5, 10, 20, 30 Minute Intervals

You can use any of the code snippets above and simply set @interval_mins value to 5, 10, 20, or 30 to group your Data/Time to your desired X minute interval.

For example, setting @interval_mins variable to 5 using the round to nearest technique, you can retrieve aggregated login results in 5 minute interval.

SET @begin_datetime = '1000-01-01 00:00:00';
SET @interval_mins = 5;

SELECT CASE
		WHEN MOD(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins, 1) >= 0.5
			THEN DATE_ADD(@begin_datetime, Interval CEILING(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		ELSE DATE_ADD(@begin_datetime, Interval FLOOR(TIMESTAMPDIFF(MINUTE, @begin_datetime, login_datetime) / @interval_mins) * @interval_mins minute)
		END AS login_datetime_5_min_interval,
	COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY login_datetime_5_min_interval
ORDER BY login_datetime_5_min_interval;
login_datetime_5_min_intervaldistinct_account_logins
2022-03-02 02:05:001
2022-03-02 02:10:001
2022-03-02 02:15:002
2022-03-02 02:25:001
2022-03-02 02:30:002
2022-03-02 02:35:001
2022-03-02 02:40:001
2022-03-02 02:45:001
2022-03-02 02:55:001
2022-03-02 03:00:001

Same data grouped by 10 minute interval - @interval_mins = 10

login_datetime_10_min_intervaldistinct_account_logins
2022-03-02 02:10:003
2022-03-02 02:20:001
2022-03-02 02:30:003
2022-03-02 02:40:002
2022-03-02 02:50:002
2022-03-02 03:00:001

Same data grouped by 20 minute interval - @interval_mins = 20

login_datetime_20_min_intervaldistinct_account_logins
2022-03-02 02:00:002
2022-03-02 02:20:004
2022-03-02 02:40:004
2022-03-02 03:00:002

Same data grouped by 30 minute interval - @interval_mins = 30

login_datetime_30_min_intervaldistinct_account_logins
2022-03-02 02:00:003
2022-03-02 02:30:006
2022-03-02 03:00:003