从Redshift卸载数据到S3时丢失毫秒精度的解决方法

当你用Redshift的UNLOAD命令导出数据到S3时，默认的datetime输出会自动截断毫秒部分末尾的零，导致原本3位精度的时间数据丢失格式一致性。要解决这个问题，核心是显式格式化timestamp列，强制保留三位毫秒精度。

具体修改方法 #

修改UNLOAD语句中的SELECT子查询，对每个datetime类型的列使用TO_CHAR函数，指定包含固定三位毫秒的格式字符串：'YYYY-MM-DD HH24:MI:SS.FFF'。

修改后的完整UNLOAD命令如下：

UNLOAD ('SELECT "integer", TO_CHAR("timestamp", ''YYYY-MM-DD HH24:MI:SS.FFF''), TO_CHAR("timestamp22", ''YYYY-MM-DD HH24:MI:SS.FFF'') FROM "test_db"."test_table"') 
TO 's3://bucket/location' 
ENCRYPTED 
ACCESS_KEY_ID '<redacted>' 
SECRET_ACCESS_KEY '<redacted>' 
ESCAPE 
ADDQUOTES 
MANIFEST 
DELIMITER AS ','

效果说明 #

执行上述命令后，S3中的导出数据会完整保留三位毫秒精度，示例数据会变为：

"1", "2023-11-24 12:30:35.123", "2023-11-24 12:30:25.000"
"2", "2023-11-24 12:30:35.120", "2023-11-24 12:30:25.200"

补充说明 #

如果后续需要将S3中的数据重新加载回Redshift，可以使用TO_TIMESTAMP函数将格式化后的字符串转回timestamp类型，示例加载语句片段：

SELECT "integer", TO_TIMESTAMP(timestamp_str, 'YYYY-MM-DD HH24:MI:SS.FFF') AS "timestamp", TO_TIMESTAMP(timestamp22_str, 'YYYY-MM-DD HH24:MI:SS.FFF') AS "timestamp22"
FROM ...

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