PostgreSQL中如何将varchar类型时间字符串转换为timestamp类型

你存储的2019-09-28T23:59:59.52Z属于标准ISO 8601带UTC时区标识的时间格式，PostgreSQL原生支持该格式的直接转换，无需额外做字符串裁剪处理。

1. 生产可用修改语句 #

1.1 保留毫秒值的转换方案 #

你实测有效的语句即可满足需求，转换后会自动保留.52的毫秒部分，格式为2019-09-28 23:59:59.52：

ALTER TABLE my_table ALTER COLUMN my_column TYPE TIMESTAMP USING my_column::timestamp without time zone;

1.2 截断到秒的转换方案 #

如果你不需要保留毫秒，最终存储的时间精确到秒，可使用date_trunc函数做截断处理：

ALTER TABLE my_table ALTER COLUMN my_column TYPE TIMESTAMP USING date_trunc('second', my_column::timestamp without time zone);

2. 操作前置校验建议 #

正式修改列类型前，建议先执行查询验证转换结果是否符合预期，避免数据异常：

SELECT 
  my_column AS 原始varchar值,
  my_column::timestamp without time zone AS 带毫秒转换结果,
  date_trunc('second', my_column::timestamp without time zone) AS 截断到秒转换结果
FROM my_table LIMIT 20;

如果表数据量较大，建议在业务低峰期执行修改操作，避免锁表影响正常业务。

3. 查询自定义格式补充说明 #

timestamp类型为二进制存储，本身无固定字符串展示格式。如果查询时需要固定输出YYYY-MM-DD HH24:MI:SS样式的字符串，可使用to_char函数做查询转换：

SELECT to_char(my_column, 'YYYY-MM-DD HH24:MI:SS') AS 格式化后时间 FROM my_table;

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