TIMESTAMPDIFF函数始终返回NULL的问题排查求助

你的查询始终返回NULL，核心原因是JSON_EXTRACT提取的日期字符串包含双引号，导致STR_TO_DATE无法正确解析。

问题复现 #

原查询语句：

SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(JSON_EXTRACT(VALUE_, '$.END_DATE'), '%Y-%m-%d %H:%i:%s')) AS SECONDS_LEFT FROM SETTINGS WHERE KEY_ = 'MAINTENANCE'

表结构与数据：

CREATE TABLE `settings` (
  `KEY_` char(50) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE_` json NOT NULL,
  UNIQUE KEY `KEY_` (`KEY_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

原因分析 #

执行JSON_EXTRACT(VALUE_, '$.END_DATE')会返回带双引号的字符串："2021-01-07 23:46:53"，而你的STR_TO_DATE格式串%Y-%m-%d %H:%i:%s没有匹配这些引号，导致日期转换失败返回NULL，最终TIMESTAMPDIFF也返回NULL。

解决方案 #

提供两种简洁有效的修复方式：

方式1：使用JSON_UNQUOTE去除引号

SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(VALUE_, '$.END_DATE')), '%Y-%m-%d %H:%i:%s')) AS SECONDS_LEFT 
FROM SETTINGS 
WHERE KEY_ = 'MAINTENANCE';

方式2：使用->>运算符（MySQL 5.7+推荐）

->>是JSON_UNQUOTE(JSON_EXTRACT(...))的简写，代码更简洁：

SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(VALUE_->>'$.END_DATE', '%Y-%m-%d %H:%i:%s')) AS SECONDS_LEFT 
FROM SETTINGS 
WHERE KEY_ = 'MAINTENANCE';

可选方式：修改格式串匹配引号（不推荐）

如果不想修改JSON提取逻辑，也可以在格式串中加入双引号，但这种写法不够直观：

SELECT TIMESTAMPDIFF(SECOND, NOW(), STR_TO_DATE(JSON_EXTRACT(VALUE_, '$.END_DATE'), '"%Y-%m-%d %H:%i:%s"')) AS SECONDS_LEFT 
FROM SETTINGS 
WHERE KEY_ = 'MAINTENANCE';

验证步骤 #

你可以先单独执行以下语句确认提取结果：

SELECT JSON_EXTRACT(VALUE_, '$.END_DATE') FROM SETTINGS WHERE KEY_ = 'MAINTENANCE';

返回结果为"2021-01-07 23:46:53"，确认带双引号后，再使用上述修复方法即可得到正确的剩余时长。

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