如何在Athena Redshift SQL中将ISO时间字符串转为Unix时间戳？

嘿，我来帮你搞定这个时间格式转换的需求！因为Athena（基于Presto）和Redshift的SQL函数体系略有不同，我分别给你两种环境下的具体实现方案：

一、Athena（Presto）环境下的转换方法 #

你的unix_timestamp列是带UTC后缀的ISO 8601格式字符串，Athena可以通过两步完成转换：

1. 用parse_datetime函数将字符串解析为时间类型
2. 用to_unixtime函数将时间类型转为秒级Unix时间戳

示例代码 #

SELECT 
  -- 单值转换示例
  to_unixtime(parse_datetime('2018-04-01T10:05:52.047Z', 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''')) AS timestamp_code,
  -- 针对表列的转换示例
  to_unixtime(parse_datetime(your_table.unix_timestamp, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''')) AS converted_timestamp
FROM your_table;

补充说明 #

如果需要和示例1514564885一致的整数格式时间戳，可以用cast()转成整数：

SELECT cast(to_unixtime(parse_datetime(your_table.unix_timestamp, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''')) AS bigint) AS timestamp_code
FROM your_table;

格式字符串里的''T''和''Z''是为了转义字符串中的固定字符T和Z，确保解析准确。

二、Redshift环境下的转换方法 #

Redshift用不同的函数组合来实现同样的效果：

1. 用to_timestamp函数解析ISO格式字符串
2. 用extract(epoch from ...)提取Unix时间戳（秒级）

示例代码 #

SELECT 
  -- 单值转换示例
  extract(epoch from to_timestamp('2018-04-01T10:05:52.047Z', 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'))::bigint AS timestamp_code,
  -- 针对表列的转换示例
  extract(epoch from to_timestamp(your_table.unix_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.MS"Z"'))::bigint AS converted_timestamp
FROM your_table;

补充说明 #

::bigint是Redshift的类型转换语法，把提取出的浮点型秒数转成整数，和你的目标格式完全匹配。格式字符串中的"T"和"Z"用于转义固定字符，HH24表示24小时制，MS匹配毫秒部分。

注意事项 #

确保你的unix_timestamp列所有值都符合yyyy-MM-ddTHH:mm:ss.SSSZ格式，否则转换会报错。如果有格式不统一的数据，可以先用try_parse_datetime（Athena）或try_to_timestamp（Redshift）做容错处理，避免查询失败。

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