Rounding MySQL dates to nearest interval without being affected by Daylight saving changes

Question

SET @time := '2018-11-08 00:36:50.000000';
SET @intervalInMinutes := 15
SELECT FROM_UNIXTIME((Select FLOOR ((select UNIX_TIMESTAMP(@time) / (@intervalInMinutes* 60))) * (@intervalInMinutes*60)));

I need to write a query in MySQL that converts the date/time in the nearest interval category specified by the user. Examples:

• Eg 1: '2018-10-24 17:45:50.000000'

  1. For an interval of 15 mins would translate to '2018-10-24 17:45:00'
  2. For an interval of 30 mins would translate to '2018-10-24 17:30:00'
  3. For an interval of 60 mins would translate to '2018-10-24 17:00:00'

• Eg 2: '2018-10-24 17:36:20.000000';

  1. For an interval of 15 mins would translate to '2018-10-24 17:30:00'
  2. For an interval of 30 mins would translate to '2018-10-24 17:30:00'
  3. For an interval of 60 mins would translate to '2018-10-24 17:00:00'

I have written the above query which seems to work fine. However, going by this post , the first answer has a comment that states that this approach will fail datetimes that fall between the daylight time savings. I dont get the example he specifies. The other approaches I notice on that thread looks even more complicated for my case. I am trying to understand how this would affect my scenario. Any suggestions/examples are appreciated.Or, is there a better way to avoid this by writing a simpler query ? P.S. I want this to be applicable to MySQL 5.6,5.7 and 8.0.

Answer 1

The issue that you can face while using converting datetime to unixtime and back is quite OK explained in MySQL database.

So to be on a safe side I'd use another approach:

SET @time := '2018-11-08 00:36:50.000000';
SET @intervalInMinutes := 15;
SELECT TIMESTAMP(DATE(@time), SEC_TO_TIME(FLOOR(TIME_TO_SEC(TIME(@time)) / (@intervalInMinutes * 60)) * (@intervalInMinutes * 60)));

The idea is very similar: you are converting time to seconds, rounding and converting back.