Mysql: How to round TIME values to the nearest period - 10, 15, 20, 30 minutes.

We can round time values using these formulas:

(time_in_minutes DIV period_in_minutes) * period_in_minutes

or

(time_in_seconds DIV period_in_minutes * 60) * period_in_minutes * 60

Where time_in_minutes and time_in_seconds are time values we heed to round, and period_in_minutes is rounding period in minutes.

There are two good functions in MySQL that allow converting TIME values to seconds and vice-versa, they are TIME_TO_SEC and SEC_TO_TIME. We can apply these functions in our task; so, MySQL variant look like this -

SEC_TO_TIME((TIME_TO_SEC(time_field) DIV period_in_minutes * 60) * period_in_minutes * 60)
 

Examples:

Our table -

CREATE TABLE time_table(
  time_column TIME,
  column_a VARCHAR(20)
);
INSERT INTO time_table VALUES 
  ('09:00:10', 'text1'),
  ('09:05:00', 'text2'),
  ('09:08:30', 'text3'),
  ('09:15:20', 'text4'),
  ('09:21:00', 'text5'),
  ('09:25:20', 'text6'),
  ('11:15:00', 'text7');
SELECT * FROM time_table;
+-------------+----------+
| time_column | column_a |
+-------------+----------+
| 09:00:10    | text1    |
| 09:05:00    | text2    |
| 09:08:30    | text3    |
| 09:15:20    | text4    |
| 09:21:00    | text5    |
| 09:25:20    | text6    |
| 11:15:00    | text7    |
+-------------+----------+

To round time_column values by periods of 10 minutes we need to use (10 * 60) which is equal to 600:

SELECT
  SEC_TO_TIME((TIME_TO_SEC(time_column) DIV 600) * 600) AS 10_min_period
FROM
  time_table; 
+---------------+----------+
| by_10_minutes | column_a |
+---------------+----------+
| 09:00:00      | text1    |
| 09:00:00      | text2    |
| 09:00:00      | text3    |
| 09:10:00      | text4    |
| 09:20:00      | text5    |
| 09:20:00      | text6    |
| 11:10:00      | text7    |
+---------------+----------+

Round by 20 minutes:

SELECT
  SEC_TO_TIME((TIME_TO_SEC(time_column) DIV 1200) * 1200) AS by_20_minutes,
  column_a
FROM
  time_table;
+---------------+----------+
| by_20_minutes | column_a |
+---------------+----------+
| 09:00:00      | text1    |
| 09:00:00      | text2    |
| 09:00:00      | text3    |
| 09:00:00      | text4    |
| 09:20:00      | text5    |
| 09:20:00      | text6    |
| 11:00:00      | text7    |