MYSQL Round the datetime to 15 minute

Issue

I want to update the datetime round to 15 minutes in a MYSQL database table.

For Example:

If the dateTime is 2013-10-08 10:36:00, I want to convert it into 2013-10-08 10:30:00
Similarly,2013-10-08 10:22:00 to 2013-10-08 10:15:00

I have seen this answer but it converts datetime into seconds and return time only, I want date as well.

Thanks

Solution

The answer you have seen is quite useful, try this

SELECT SUBSTRING_INDEX(datetime_field, ' ', -1) AS old_time,SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900) AS rounded_time, datetime_field FROM yourtable

You can get time from the datetime_field as substring and replace it with the rounded time.

If you want to update the datetime you can reply it and update it with update function:

UPDATE yourtable SET `datetime_field` =  REPLACE(datetime_filed,SUBSTRING_INDEX(datetime_field, ' ', -1),SEC_TO_TIME((TIME_TO_SEC(datetime_field) DIV 900) * 900))

Answered By – AL̲̳I

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published