I just started learning AWS Athena and I want to group date strings into intervals but my case statements aren't working. I'm basically trying to create buckets. Can someone tell me what I'm doing wrong here?
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;
I've updated the query and receiving
SYNTAX_ERROR: line 3:11: Cannot check if varchar is BETWEEN timestamp and timestamp
I think I understand the issue now.
The values are strings; therefore; how would it know what's between 12:00:00 AM and 03:00:00 AM. Any ideas on a workaround?
Here is what I have now.
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;
The error message is : Amazon QuickSight can’t parse the data because it contains invalid dates or invalid numbers. Make sure your data contains only supported number and date formats.