MySQL JSON类型字段按日期查询问题求助

我之前也碰到过一模一样的问题！大概率是两个核心原因导致转换返回NULL：要么是JSON提取的日期值自带双引号没处理，要么是日期格式和转换函数不匹配，咱们一步步来排查解决：

1. 先解决最常见的坑：JSON提取的字符串带双引号 #

当你用readouts->"$.temperature_sensor_1.readTimestamp"提取JSON字段时，返回的结果是带双引号的字符串（比如"2024-05-20 14:30:00"），直接用DATE()或STR_TO_DATE()转换会因为引号识别失败，返回NULL。

解决方法有两种：

• 用->>代替->，这是JSON_EXTRACT()+JSON_UNQUOTE()的简写，自动去掉双引号：

  SELECT *
  FROM your_table
  WHERE DATE(readouts->>"$.temperature_sensor_1.readTimestamp") = '2024-05-20';
  

• 手动加JSON_UNQUOTE()函数：

  SELECT *
  FROM your_table
  WHERE DATE(JSON_UNQUOTE(readouts->"$.temperature_sensor_1.readTimestamp")) = '2024-05-20';
  

2. 根据JSON里日期的实际格式调整转换方式 #

如果上面的方法还是不行，那就要看你JSON里readTimestamp的实际格式了：

情况A：日期是Unix时间戳（数字类型） #

如果readTimestamp存的是数字格式的时间戳（比如1716182400），那不能直接用DATE()，得先转成datetime：

-- 秒级时间戳
SELECT *
FROM your_table
WHERE DATE(FROM_UNIXTIME(readouts->"$.temperature_sensor_1.readTimestamp")) = '2024-05-20';

-- 如果是毫秒级时间戳（比如1716182400000），要除以1000
SELECT *
FROM your_table
WHERE DATE(FROM_UNIXTIME(readouts->"$.temperature_sensor_1.readTimestamp" / 1000)) = '2024-05-20';

情况B：日期是自定义格式的字符串 #

如果readTimestamp是非标准格式的字符串（比如20/05/2024或05-20-2024），需要用STR_TO_DATE()指定对应格式符：

-- 示例：格式为DD/MM/YYYY
SELECT *
FROM your_table
WHERE STR_TO_DATE(readouts->>"$.temperature_sensor_1.readTimestamp", '%d/%m/%Y') = '2024-05-20';

-- 示例：格式为MM-DD-YYYY HH:MM:SS
SELECT *
FROM your_table
WHERE STR_TO_DATE(readouts->>"$.temperature_sensor_1.readTimestamp", '%m-%d-%Y %H:%i:%s') = '2024-05-20 14:30:00';

3. 额外排查点 #

• 先确认JSON路径是否正确：可以单独运行SELECT readouts->"$.temperature_sensor_1.readTimestamp" FROM your_table LIMIT 5;，看看返回的内容是不是你预期的日期值，有没有拼写错误（比如sensor写成sensor1）。
• 排除空值：有些行可能没有这个JSON键，转换后会是NULL，你可以加条件AND readouts->"$.temperature_sensor_1.readTimestamp" IS NOT NULL过滤这些行。

内容的提问来源于stack exchange，提问作者Adam Mołoniewicz