GMT夏令时变更后SQL查询返回负值问题求助

夏令时切换导致部分记录的completed_time早于date_created（比如时间回拨后，切换前的01:30会比切换后的01:10更早），使得TIMEDIFF(completed_time, date_created)返回负数。当同时按DATE(date_created)和tier分组时，某个分组的平均时间差变为负数，而SEC_TO_TIME函数返回的负时间格式（如-00:06:33.6250）不符合MySQL的TIME类型解析规则，从而触发报错。单独分组时，分组粒度更粗，平均时间差仍为正数，因此不会报错。

方案1：改用UTC时间计算，规避夏令时影响 #

将时间字段转换为UTC后计算时间差，彻底避免夏令时切换带来的时间回拨问题：

SELECT 
  DATE(date_created),
  TIME(date_created),
  TIME(completed_time),
  SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, CONVERT_TZ(date_created, @@session.time_zone, '+00:00'), CONVERT_TZ(completed_time, @@session.time_zone, '+00:00')))) AS avg_time_diff,
  AVG(TIMESTAMPDIFF(SECOND, CONVERT_TZ(date_created, @@session.time_zone, '+00:00'), CONVERT_TZ(completed_time, @@session.time_zone, '+00:00')))/3600 AS avg_hours
FROM leads 
WHERE date_created > DATE_SUB(NOW(), INTERVAL 60 DAY) 
GROUP BY DATE(date_created), tier
ORDER BY date_created DESC

方案2：处理负时间差，生成合法格式 #

如果需要保留负时间差的统计结果，先计算平均秒数，再手动处理正负格式：

SELECT 
  date_date,
  time_created,
  time_completed,
  CASE 
    WHEN avg_sec >= 0 THEN SEC_TO_TIME(avg_sec)
    ELSE CONCAT('-', SEC_TO_TIME(-avg_sec))
  END AS avg_time_diff,
  avg_sec/3600 AS avg_hours
FROM (
  SELECT 
    DATE(date_created) AS date_date,
    tier,
    TIME(date_created) AS time_created,
    TIME(completed_time) AS time_completed,
    AVG(TIMESTAMPDIFF(SECOND, date_created, completed_time)) AS avg_sec
  FROM leads 
  WHERE date_created > DATE_SUB(NOW(), INTERVAL 60 DAY) 
  GROUP BY date_date, tier, time_created, time_completed
) AS sub_query
ORDER BY date_date DESC

方案3：过滤异常负时间差记录 #

如果认定completed_time不应早于date_created，可以直接过滤掉这些异常记录：

SELECT 
  DATE(date_created),
  TIME(date_created),
  TIME(completed_time),
  SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(completed_time,date_created)))), 
  AVG(TIMEDIFF(completed_time,date_created)) 
FROM leads 
WHERE date_created > DATE_SUB(NOW(), INTERVAL 60 DAY) 
  AND completed_time >= date_created
GROUP BY DATE(date_created), tier
ORDER BY date_created DESC

内容的提问来源于stack exchange，提问作者andy_coops