How to round TIME values to the nearest period – 10, 15, 20, 30 minutes.

How to round TIME values to the nearest period – 10, 15, 20, 30 minutes.

We can round time values using these formulas:

or

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 –

 

Examples:

 

Our table –

 

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

 

Round by 20 minutes:

Leave a Reply

Your email address will not be published. Required fields are marked *