MySQL DATE_FORMAT返回异常结果及时间转换问题求助

原因 #

1. 格式不兼容：你用到的时间字符串2099-01-12T23:59:59.999999999:00不符合MySQL可识别的标准格式——末尾的:00不是合法的时区标识（标准时区应为+00:00或-00:00格式），导致MySQL无法正确解析整个字符串。
2. 精度限制：MySQL的时间类型（DATETIME、TIME等）最多仅支持6位微秒，而字符串里带了9位纳秒，超出的精度会触发解析异常。
3. 这两个问题叠加，使得CAST(...) AS TIME直接返回null；DATE_FORMAT则因为解析失败，错误地将时间识别为次日的00:00:00。
4. 补充：该字符串来自Java的LocalDateTime类型，但LocalDateTime本身是无时区信息的，末尾的:00属于多余的错误输出，大概率是Java端格式化时的疏漏。

解决方法 #

方案1：从Java端源头修正（推荐） #

既然LocalDateTime不带时区，直接调整格式化规则，去掉多余的时区后缀，同时将纳秒截断到6位以适配MySQL的精度：

// Java代码示例
LocalDateTime dt = LocalDateTime.of(2099, 1, 12, 23, 59, 59, 999999999);
// 格式化时保留6位微秒，不带任何时区相关后缀
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSSSS");
String formattedStr = dt.format(formatter);
// 输出结果：2099-01-12T23:59:59.999999

将这个格式化后的字符串传入MySQL，执行DATE_FORMAT(formattedStr, '%H:%i:%s')就能得到期望的23:59:59，CAST(formattedStr AS TIME)也会正常返回23:59:59.999999。

方案2：在MySQL端处理已有错误格式的字符串 #

如果无法修改Java端的输出，可在MySQL中先清洗字符串，再进行解析：

-- 正则替换：截断9位纳秒为6位，同时去掉末尾多余的:00
SELECT DATE_FORMAT(
    STR_TO_DATE(
        REGEXP_REPLACE('2099-01-12T23:59:59.999999999:00', '\\.(\\d{6})\\d{3}:00$', '.\\1'),
        '%Y-%m-%dT%H:%i:%s.%f'
    ), '%H:%i:%s'
);

执行后即可得到23:59:59。如果你的字符串末尾的:00长度固定，也可以用SUBSTRING函数直接截取：

SELECT DATE_FORMAT(
    STR_TO_DATE(
        SUBSTRING('2099-01-12T23:59:59.999999999:00', 1, 26), -- 截取到6位微秒的位置
        '%Y-%m-%dT%H:%i:%s.%f'
    ), '%H:%i:%s'
);

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