Skip to main content

SQL Server T-SQL: 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 very 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 T-SQL snippets to round Date/Time records to the 15 minute increment and group them by 15 minute intervals.

In addition, we'll demonstrate how we can re-use these T-SQL scripts to group data by 5, 10, 20, and 30 minute intervals if asked by the business stakeholders.

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

Example Data​

create table login_logs (
    account_id INT,
    login_datetime DATETIME2(0)
);
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 DATEDIFF function to calculate the minute difference between the login_datetime and SQL Server begin_datetime '1753-01-01 00:00:00'
  2. Divide the calculated minute difference by @interval_mins which has a value of 15. Remember to CAST the value in @interval_mins variable to DECIMAL.
  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 SQL Server begin_datetime to get the new round up login_datetime values in 15 minute intervals
DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    DATEDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
    CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins AS diff_round_up,
    DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) 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.

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval,
    COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
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.

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    DATEDIFF(MINUTE, @begin_datetime, login_datetime) AS diff,
    FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins AS diff_round_down,
    DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) 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.

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime) AS login_datetime_15_min_interval,
    COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
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 DATEDIFF function to calculate the minute difference between the login_datetime and SQL Server begin_datetime '1753-01-01 00:00:00'
  2. Divide the calculated minute difference by 15
  3. Use % operator 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
DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT account_id,
    login_datetime,
    (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 AS nearest_indicator,
    CASE
        WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
            THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
        ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
        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.

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 15;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_15_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
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 re-use any of the T-SQL scripts above and simply set @interval_mins value to 5, 10, 20, or 30 to group your Data/Time to generate 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.

DECLARE @begin_datetime DATETIME = '1753-01-01 00:00:00';
DECLARE @interval_mins INT = 5;

SELECT CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END AS login_datetime_5_min_interval
	,COUNT(DISTINCT (account_id)) AS distinct_account_logins
FROM login_logs
GROUP BY CASE 
		WHEN (DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) % 1 >= 0.5
			THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		ELSE DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, @begin_datetime, login_datetime) / CAST(@interval_mins AS DECIMAL)) * @interval_mins, @begin_datetime)
		END
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 can be grouped by 10 minute interval by setting @interval_mins to 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 can be grouped by 10 minute interval by setting @interval_mins to 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 can be grouped by 10 minute interval by setting @interval_mins to 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