mysql - 将 MySQL 日期四舍五入到最近的间隔，而不受夏令时变化的影响

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)));

我需要在 MySQL 中编写一个查询，将日期/时间转换为用户指定的最近间隔类别。示例:

• 例如 1:'2018-10-24 17:45:50.000000'

  1. 间隔 15 分钟将转换为“2018-10-24 17:45:00”
  2. 间隔 30 分钟将转换为“2018-10-24 17:30:00”
  3. 对于 60 分钟的间隔将转换为“2018-10-24 17:00:00”

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

  1. 对于 15 分钟的间隔将转换为“2018-10-24 17:30:00”
  2. 30 分钟的间隔将转换为“2018-10-24 17:30:00”
  3. 对于 60 分钟的间隔将转换为“2018-10-24 17:00:00”

我已经编写了上面的查询，它似乎工作正常。然而，通过this post ，第一个答案有一条评论指出此方法将在夏令时之间的日期时间失败。我没有得到他指定的例子。我在该线程上注意到的其他方法对于我的情况来说看起来更加复杂。我试图了解这将如何影响我的场景。任何建议/示例表示赞赏。或者，是否有更好的方法通过编写更简单的查询来避免这种情况？附言我希望它适用于 MySQL 5.6、5.7 和 8.0。

Answer 1

在将 datetime 转换为 unixtime 并返回时您可能会遇到的问题很正常 explained in MySQL database .

所以为了安全起见，我会使用另一种方法:

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)));

想法非常相似:您将时间转换为秒，四舍五入并转换回来。