Athena中基于字符串类型birth_dt列计算18个月前的成员年龄

问题根源 #

1. birth_dt为字符串类型，未转换为时间戳就传入DATE_DIFF，导致参数类型不匹配
2. 之前的日期转换函数date_parse使用了错误的格式字符串，无法匹配"1990-08-15 00:00:00.000"的空格分隔+毫秒格式

正确解法 #

步骤1：精准转换字符串为时间戳 #

针对你的日期格式，使用date_parse并搭配正确的格式模板：

date_parse(birth_dt, '%Y-%m-%d %H:%i:%s.%f')

模板各部分对应：

• %Y：4位年份 | %m：2位月份 | %d：2位日期
• 空格：匹配日期与时间的分隔符
• %H：24小时制小时 | %i：分钟 | %s：秒 | %f：毫秒

步骤2：计算18个月前的年龄 #

方式一：精准年龄计算（考虑生日未过的情况）

避免DATE_DIFF在生日前计算时多算一岁的问题：

SELECT
  EXTRACT(YEAR FROM target_date) - EXTRACT(YEAR FROM birth_ts)
  - CASE
      WHEN (EXTRACT(MONTH FROM target_date), EXTRACT(DAY FROM target_date)) 
           < (EXTRACT(MONTH FROM birth_ts), EXTRACT(DAY FROM birth_ts))
      THEN 1
      ELSE 0
    END AS age
FROM (
  SELECT
    date_parse(birth_dt, '%Y-%m-%d %H:%i:%s.%f') AS birth_ts,
    current_timestamp - INTERVAL '18' MONTH AS target_date
  FROM table.DB
) t
LIMIT 10;

方式二：简化版（忽略生日未过的精度）

如果不需要严格的生日精度，直接转换后使用DATE_DIFF：

SELECT
  DATE_DIFF('year', date_parse(birth_dt, '%Y-%m-%d %H:%i:%s.%f'), current_timestamp - INTERVAL '18' MONTH) AS age
FROM table.DB
LIMIT 10;

之前尝试的错误说明 #

• date_parse('%YYYY-%MM-%dd')：格式模板错误（多写了一个Y，且未包含时间/毫秒部分）
• from_iso8601_timestamp("birth_dt.1")：列名错误，且ISO8601格式是T分隔日期时间，与你的空格分隔不匹配
• 直接调用DATE_DIFF：未将字符串类型的birth_dt转换为时间戳，导致参数类型不兼容

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