Athena中如何将带时区偏移的字符串转换为datetime(3)类型

你存储的时间字符串格式符合yyyy-MM-dd HH:mm:ss.SSS±HHMM标准带时区偏移格式，直接用Athena内置的日期函数即可完成转换，具体实现如下：

核心SQL实现 #

假设存储时间字符串的列名为datetime_str，你的表名为your_table，查询语句如下：

SELECT
  -- 转换为timestamp(3)类型（即datetime(3)，精度到毫秒）
  date_trunc(
    'millisecond',
    at_timezone(
      date_parse(datetime_str, '%Y-%m-%d %H:%i:%s.%f%z'),
      'UTC'
    )
  ) AS converted_datetime
FROM your_table

如果你需要输出为2021-08-17 10:55:22.819格式的字符串，可使用date_format函数：

SELECT
  date_format(
    at_timezone(
      date_parse(datetime_str, '%Y-%m-%d %H:%i:%s.%f%z'),
      'UTC'
    ),
    '%Y-%m-%d %H:%i:%s.%f'
  ) AS converted_datetime_str
FROM your_table

函数说明 #

• date_parse：按照指定格式将字符串解析为带时区的timestamp类型，格式串匹配规则如下：
  • %Y：匹配4位年份
  • %m：匹配2位月份
  • %d：匹配2位日期
  • %H：匹配24小时制的2位小时
  • %i：匹配2位分钟
  • %s：匹配2位秒
  • %f：匹配秒的小数部分（自动兼容3位毫秒值）
  • %z：匹配±HHMM格式的时区偏移，兼容你数据里的-0400这类值
• at_timezone(解析后的时间, 'UTC')：将带时区的时间转换为UTC时区的时间，刚好满足你示例中-4时区转UTC的偏移要求
• date_trunc('millisecond', xxx)：将时间精度截断到毫秒，对应datetime(3)的精度要求

注意事项 #

如果存在部分数据格式不规范的情况，可以用try()函数包裹date_parse逻辑，格式错误的数据会返回null，不会导致整个查询报错：

SELECT
  date_trunc(
    'millisecond',
    at_timezone(
      try(date_parse(datetime_str, '%Y-%m-%d %H:%i:%s.%f%z')),
      'UTC'
    )
  ) AS converted_datetime
FROM your_table

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