Athena从varchar类型Timestamp列提取日期、年、月、周的实现方法

问题前提 #

Athena（基于Presto引擎）中createddate字段为varchar类型，存储时间戳格式为「月/日/年 时:分:秒」，典型值如11/29/2016 10:58:02，需要从中提取日期、年、月、周维度信息，原有写法全部执行报错。原有错误测试语句如下：

select * from table where YEAR(from_iso8601_timestamp(createddate)) = 2022
and MONTH(from_iso8601_timestamp(createddate)) = 6 limit 10

select date_format(createddate,'%m/%d/%Y %H:%m:%s') from table limit 10

select date_format(createddate,'%m/%d/%Y H:m:s') from table limit 10

select date_format(createddate,'%m/%d/%Y %H:%i:%s') from table limit 10

select date_parse(createddate, '%m/%d/%Y %H:%i%p') from table limit 10

select date_parse(createddate, ' %m/%d/%Y') from table limit 10

select cast(date_parse(createddate,'%Y-%m-%d %h24:%i:%s') as date) from table limit 10

Select CAST(date_format(date_parse(cast(createddate as varchar(10)), '%m%d%Y'), '%m/%d/%Y') AS DATE) from table limit 10

错误原因 #

• 函数误用：from_iso8601_timestamp仅支持解析ISO8601标准格式时间串（如2016-11-29T10:58:02），和当前字段的「月/日/年 时:分:秒」格式完全不匹配。
• 入参类型错误：date_format函数要求入参为date/timestamp类型，直接传入varchar类型字段会触发类型不匹配报错。
• 格式串不匹配：
  • 格式符使用错误：如用%m指代分钟（%m实际代表月份）、写不存在的%h24格式符、漏写%前缀、混用其他数据库的格式规则
  • 格式串和实际值结构不符：如加了字段中不存在的AM/PM标记%p、把日期顺序写为「年-月-日」和实际的「月/日/年」顺序相反、截取字符串长度不对导致斜杠等分隔符无法匹配。

正确实现 #

核心解析逻辑 #

先用date_parse把varchar字段严格按照存储格式转为标准timestamp类型，再基于timestamp做维度提取，基础解析语句为：

date_parse(createddate, '%m/%d/%Y %H:%i:%s')

对应格式符说明：

• %m：两位月份
• %d：两位日期
• %Y：四位年份
• %H：24小时制两位小时
• %i：两位分钟
• %s：两位秒

各维度提取写法 #

• 完整日期（date类型）：CAST(date_parse(createddate, '%m/%d/%Y %H:%i:%s') AS DATE)
• 年份：YEAR(date_parse(createddate, '%m/%d/%Y %H:%i:%s'))
• 月份：MONTH(date_parse(createddate, '%m/%d/%Y %H:%i:%s'))
• 周数（自然周，周起始为周日）：week_of_year(date_parse(createddate, '%m/%d/%Y %H:%i:%s'))
• ISO标准周数（周起始为周一）：week(date_parse(createddate, '%m/%d/%Y %H:%i:%s'))

完整查询示例 #

查询2022年6月的10条数据并带出所有时间维度：

SELECT 
  *,
  CAST(date_parse(createddate, '%m/%d/%Y %H:%i:%s') AS DATE) AS dt,
  YEAR(date_parse(createddate, '%m/%d/%Y %H:%i:%s')) AS year_val,
  MONTH(date_parse(createddate, '%m/%d/%Y %H:%i:%s')) AS month_val,
  week_of_year(date_parse(createddate, '%m/%d/%Y %H:%i:%s')) AS week_val
FROM your_table
WHERE YEAR(date_parse(createddate, '%m/%d/%Y %H:%i:%s')) = 2022
  AND MONTH(date_parse(createddate, '%m/%d/%Y %H:%i:%s')) = 6
LIMIT 10

表数据量较大时，可以提前把解析逻辑做成视图，避免每次查询重复写解析语句。

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