AWS Athena字符串时间分组Case When语法及解析问题求助

我刚学习AWS Athena，想要将字符串类型的时间字段分组为时间区间桶，但Case When语句运行异常：

• 初始查询因varchar类型无法与timestamp类型比较报错
• 修改查询后，在Amazon QuickSight中又出现数据包含无效日期/数字的解析错误

初始查询代码 #

Select 
Case time
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('03:00:00 AM','%h:%i:%s %p') Then '12AM - 3AM'
WHEN time between date_parse('03:00:00 AM','%h:%i:%s %p') AND date_parse('06:00:00 AM','%h:%i:%s %p') Then '3AM - 6AM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '6AM - 9AM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '9AM - 12AM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '12PM - 3PM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '3PM - 6PM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '6PM - 9PM'
WHEN time between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '9AM - 12AM'
END AS
Time_Intervals
From "another_db"."clean_crime_data" Order by time limit 10;

初始报错信息 #

SYNTAX_ERROR: line 3:11: Cannot check if varchar is BETWEEN timestamp and timestamp

修改后查询代码 #

Select 
Case 
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('03:00:00 AM','%h:%i:%s %p') Then '12AM - 3AM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('03:00:00 AM','%h:%i:%s %p') AND date_parse('06:00:00 AM','%h:%i:%s %p') Then '3AM - 6AM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '6AM - 9AM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '9AM - 12AM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '12PM - 3PM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '3PM - 6PM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '6PM - 9PM'
WHEN date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') Then '9AM - 12AM'
END AS
Time_Intervals
From "another_db"."clean_crime_data" Order by time limit 10;

修改后报错信息（翻译） #

Amazon QuickSight无法解析数据，因为其中包含无效日期或无效数字。请确保您的数据仅包含受支持的数字和日期格式。

解决方案 #

1. 修复时间区间的参数错误 #

你修改后的查询里，大部分区间的date_parse参数都是复制粘贴的'12:00:00 AM'，这是核心错误。要把每个区间的起止时间补全正确，比如：

• 6AM-9AM对应date_parse('06:00:00 AM','%h:%i:%s %p')和date_parse('09:00:00 AM','%h:%i:%s %p')
• 9AM-12PM对应date_parse('09:00:00 AM','%h:%i:%s %p')和date_parse('12:00:00 PM','%h:%i:%s %p')

2. 用try_date_parse替代date_parse处理无效数据 #

date_parse在遇到不符合格式的字符串时会直接报错，而try_date_parse会返回NULL，避免查询失败，同时兼容QuickSight的解析要求：

WHEN try_date_parse(time,'%h:%i:%s %p') between date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('03:00:00 AM','%h:%i:%s %p') Then '12AM - 3AM'

3. 优化查询逻辑，减少重复解析 #

用子查询先统一转换时间，再做区间判断，代码更清晰且减少计算量：

SELECT
  CASE
    WHEN parsed_time BETWEEN date_parse('12:00:00 AM','%h:%i:%s %p') AND date_parse('03:00:00 AM','%h:%i:%s %p') THEN '12AM - 3AM'
    WHEN parsed_time BETWEEN date_parse('03:00:00 AM','%h:%i:%s %p') AND date_parse('06:00:00 AM','%h:%i:%s %p') THEN '3AM - 6AM'
    WHEN parsed_time BETWEEN date_parse('06:00:00 AM','%h:%i:%s %p') AND date_parse('09:00:00 AM','%h:%i:%s %p') THEN '6AM - 9AM'
    WHEN parsed_time BETWEEN date_parse('09:00:00 AM','%h:%i:%s %p') AND date_parse('12:00:00 PM','%h:%i:%s %p') THEN '9AM - 12PM'
    WHEN parsed_time BETWEEN date_parse('12:00:00 PM','%h:%i:%s %p') AND date_parse('03:00:00 PM','%h:%i:%s %p') THEN '12PM - 3PM'
    WHEN parsed_time BETWEEN date_parse('03:00:00 PM','%h:%i:%s %p') AND date_parse('06:00:00 PM','%h:%i:%s %p') THEN '3PM - 6PM'
    WHEN parsed_time BETWEEN date_parse('06:00:00 PM','%h:%i:%s %p') AND date_parse('09:00:00 PM','%h:%i:%s %p') THEN '6PM - 9PM'
    WHEN parsed_time BETWEEN date_parse('09:00:00 PM','%h:%i:%s %p') AND date_parse('12:00:00 AM','%h:%i:%s %p') THEN '9PM - 12AM'
    ELSE '无效时间'
  END AS Time_Intervals
FROM (
  SELECT try_date_parse(time,'%h:%i:%s %p') AS parsed_time
  FROM "another_db"."clean_crime_data"
) t
ORDER BY parsed_time
LIMIT 10;

4. 排查无效时间数据 #

先找出无法解析的时间字符串，针对性清洗或调整解析格式：

SELECT time
FROM "another_db"."clean_crime_data"
WHERE try_date_parse(time,'%h:%i:%s %p') IS NULL;

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