MariaDB中如何将ISO 8601格式JSON时间戳转为datetime？

你的问题核心是STR_TO_DATE的格式字符串完全不匹配ISO 8601的结构：原格式%y-%m-%d, %T既没对应T分隔符、Z后缀，也没处理毫秒部分，还把四位年份写成了两位（%y是两位年，%Y才是四位），所以返回NULL。

正确的转换方案 #

1. 先用STR_TO_DATE匹配ISO 8601的完整结构，把字符串转成datetime类型；
2. 再用DATE_FORMAT将datetime转成你需要的年 月 日 时:分:秒格式。

修改后的查询语句 #

SELECT
  DATE_FORMAT(
    STR_TO_DATE(JSON_VALUE(generalinfo, '$.Information.timestamp'), "%Y-%m-%dT%H:%i:%s.%fZ"),
    "%Y %m %d %H:%i:%s"
  ) AS datetime,
  JSON_VALUE(generalinfo, '$.Information.Name') AS Name
FROM rawjson
GROUP BY Name, datetime -- 注意：原GROUP BY仅Name可能导致非聚合字段值不确定，建议补充datetime或使用聚合函数
ORDER BY datetime;

关键格式符说明 #

• %Y：匹配四位年份（如2023）
• %T等价于%H:%i:%s，对应时:分:秒
• %f：匹配毫秒部分（会被DATE_FORMAT自动忽略，因为目标格式不需要毫秒）
• 直接写T和Z：匹配原时间戳里的固定分隔符和后缀

如果你的时间戳存在无毫秒的情况（比如2023-05-10T10:32:01Z），可以用REPLACE统一处理，确保格式兼容：

SELECT
  DATE_FORMAT(
    STR_TO_DATE(
      REPLACE(JSON_VALUE(generalinfo, '$.Information.timestamp'), '.000Z', 'Z'),
      "%Y-%m-%dT%H:%i:%sZ"
    ),
    "%Y %m %d %H:%i:%s"
  ) AS datetime,
  JSON_VALUE(generalinfo, '$.Information.Name') AS Name
FROM rawjson
GROUP BY Name, datetime
ORDER BY datetime;

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