SQL按15分钟时间间隔分组统计返回大量null值问题排查

问题原因 #

• 核心问题是时间格式匹配错误：你声明InitiatedDateTimeGmt的存储格式为2021-01-04 00:00:00（对应标准格式yyyy-MM-dd HH:mm:ss），但你调用unix_timestamp(InitiatedDateTimeGmt, 'MM/dd/yyyy hh:mm:ss a')时传入的解析格式是「月/日/年+12小时制+上/下午标识」的格式，和实际存储的时间格式完全不匹配，导致绝大多数时间值解析失败返回null，后续所有转换操作基于null自然也返回null，最终就出现了大量null分组。
• 其次存在冗余的时间转换操作：原语句反复在时间戳、时间字符串、时间类型之间来回转换，完全没有必要，额外增加了出错概率。

修正后的查询语句 #

SELECT 
    from_unixtime(FLOOR(UNIX_TIMESTAMP(InitiatedDateTimeGmt) / (15 * 60)) * (15 * 60)) AS GroupTime,
    COUNT(*) AS Cnt
FROM Chat
GROUP BY GroupTime
ORDER BY 1

如果使用的是MySQL环境，也可以用更易读的写法：

SELECT 
    DATE_FORMAT(InitiatedDateTimeGmt, '%Y-%m-%d %H:00:00') + INTERVAL (FLOOR(MINUTE(InitiatedDateTimeGmt)/15)*15) MINUTE AS GroupTime,
    COUNT(*) AS Cnt
FROM Chat
GROUP BY GroupTime
ORDER BY 1

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