SQL中转换ISO时间字符串为datetime并拆分日期与时间的问题

你的问题出在使用str_to_date时格式串不匹配输入的完整ISO时间结构，导致转换失败返回NULL，再用hour()处理NULL自然得到NULL。

错误原因 #

你提取小时时，str_to_date('2022-12-28T22:28:43.260781049Z',"%H:%M:%S")的格式串只指定了时分秒，但输入是包含日期、T分隔符、微秒和Z标识的完整字符串，str_to_date无法匹配，直接返回NULL。

解决方法 #

方法1：用完整格式串匹配ISO时间

指定和输入字符串完全对应的格式串，让str_to_date正确转换为datetime后再提取日期和小时：

SELECT  
    DATE(STR_TO_DATE('2022-12-28T22:28:43.260781049Z', '%Y-%m-%dT%H:%i:%s.%fZ')) AS date,
    HOUR(STR_TO_DATE('2022-12-28T22:28:43.260781049Z', '%Y-%m-%dT%H:%i:%s.%fZ')) AS hour
FROM transaction;

格式串说明：

• %Y-%m-%d：匹配年-月-日部分
• T：匹配字符串中的固定分隔符
• %H:%i:%s：匹配时:分:秒部分
• .%f：匹配微秒部分（小数点后9位也能正确识别）
• Z：匹配字符串末尾的时区标识

方法2：MySQL 8.0+ 直接CAST转换（更简洁）

MySQL 8.0及以上版本支持直接将标准ISO格式字符串转换为datetime类型，无需手动写格式串：

SELECT  
    DATE(CAST('2022-12-28T22:28:43.260781049Z' AS DATETIME)) AS date,
    HOUR(CAST('2022-12-28T22:28:43.260781049Z' AS DATETIME)) AS hour
FROM transaction;

也可以用CONVERT函数替代CAST：

SELECT  
    DATE(CONVERT('2022-12-28T22:28:43.260781049Z', DATETIME)) AS date,
    HOUR(CONVERT('2022-12-28T22:28:43.260781049Z', DATETIME)) AS hour
FROM transaction;

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