Athena跨日期与时间戳字段按1小时区间查询的问题排查

问题背景 #

从S3存储桶的CSV文件中按1小时时间间隔取数，CSV内有两个独立字符串字段：

• date：格式为6/18/2023（月/日/年）
• Timestamp：格式为20:30:24（时:分:秒）

原查询语句触发类型不匹配错误：

private static final String SIMPLE_ATHENA_QUERY_TIME = "SELECT * FROM dmat_db.dmat_kpi_tbldmat_csv_file_processed_bucket where date > '6/18/2023' and Timestamp > '6/18/2023' - interval '1' hour;";

报错堆栈：

java.lang.RuntimeException: Query Failed to run with Error Message:
TYPE_MISMATCH: line 1:119: Cannot apply operator: varchar(9) - interval day to second    
at com.dmat.kpi.controller.DMATKpiController.waitForQueryToComplete(DMATKpiController.java:103) ~[classes/:na]    
at com.dmat.kpi.controller.DMATKpiController.AthenaQuery(DMATKpiController.java:65) ~[classes/:na]

问题原因 #

1. 直接对字符串类型的日期值'6/18/2023'执行减法运算（减interval），Athena不支持字符串与时间间隔的算术操作
2. 错误地将Timestamp字段单独与日期运算结果比较，忽略了date和Timestamp需要合并为完整时间才能做有效时间范围筛选

修正后的查询语句 #

方法一：使用parse_datetime指定格式（推荐，适配自定义日期格式） #

SELECT * 
FROM dmat_db.dmat_kpi_tbldmat_csv_file_processed_bucket 
WHERE 
  parse_datetime(concat(date, ' ', Timestamp), 'MM/dd/yyyy HH:mm:ss') > 
  date_add('hour', -1, parse_datetime('6/18/2023', 'MM/dd/yyyy'));

方法二：使用timestamp函数（需日期格式符合默认识别规则） #

SELECT * 
FROM dmat_db.dmat_kpi_tbldmat_csv_file_processed_bucket 
WHERE 
  timestamp(concat(date, ' ', Timestamp)) > 
  timestamp('2023-06-18') - interval '1' hour;

关键说明 #

• concat(date, ' ', Timestamp)：将两个字符串字段拼接为完整的时间字符串（如6/18/2023 20:30:24）
• parse_datetime(..., 'MM/dd/yyyy HH:mm:ss')：按照指定格式将字符串转换为Athena可识别的timestamp类型，避免格式转换错误
• 时间间隔运算必须在timestamp类型上执行，确保操作符两边类型匹配

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