如何将2020-11-16 03:46:26.007+00格式字符串转date或timestamp

问题原因 #

你之前的解析失败核心是格式占位符用错了：

• %h是12小时制小时标识，仅适配1-12范围的小时数值，你存储的是24小时制时间，应该用%H匹配
• 末尾的%x是ISO周年份的占位符，完全不匹配你字符串尾部的+00时区标识，导致年份解析失败，才会返回0000开头的错误结果

正确写法 #

如果你的数据里尾部时区固定为+00，直接用下面的写法即可；如果时区是可变的，可以把格式串末尾的+00替换为%z（需确认你用的SQL引擎对%z的支持规则，部分引擎要求%z匹配完整的±HHMM格式）。

适用Presto/Trino/AWS Athena（支持date_parse函数的引擎） #

1. 转换为timestamp类型

SELECT date_parse(updatedat, '%Y-%m-%d %H:%i:%s.%f+00') AS updated_at_ts
FROM table 
LIMIT 10;

2. 直接提取日期部分

SELECT date(date_parse(updatedat, '%Y-%m-%d %H:%i:%s.%f+00')) AS updated_at_date
FROM table 
LIMIT 10;

如果所有字符串格式严格固定，也可以直接截取前10位转日期，性能更高：

SELECT date(left(updatedat, 10)) AS updated_at_date
FROM table 
LIMIT 10;

适用MySQL类引擎 #

用str_to_date替代date_parse即可：

SELECT DATE(STR_TO_DATE(updatedat, '%Y-%m-%d %H:%i:%s.%f+00')) AS updated_at_date
FROM table 
LIMIT 10;

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