Working with dates in Athena and Grafana

Posted: | Updated: | Tags: til aws cloud athena

Grafana has the ability to use Amazon Athena as a data source allowing you to run SQL queries to visualize data. The Athena table data types are conveniently inherited in Grafana to be used in dashboard panels. If the data types in Athena are not exactly how you’d like them in Grafana you can still apply conversion functions.

In this case the timestamp column in Athena is formatted as a string, and I do not have the ability to adjust the table in Athena (which is normally what you’d want to do). If your Athena table doesn’t have a column in a DATE, TIMESTAMP or TIME format you will not be able to natively use a panel that relies on a timestamp. You can parse the timestamp string using the parse_datetime() function that expects the string, and the format the string is in. This columns will now appear as a timestamp to Grafana.

Here’s an example query that uses this.

SELECT  parse_datetime(timestamp_str, 'YYYY-MM-dd HH:mm:ss') as timestamp, measurement 
FROM "AwsDataCatalog"."data-export"."time-series"
WHERE product = '$product';

You can get the ranges from the time selector in Grafana and incorporate that into your query to avoid over fetching data. One way to do this is to use the $__timeFilter() function, just like the function before this accepts the timestamp column as string along with the format.

Here’s an example of what this query would look like.

SELECT  parse_datetime(timestamp_str, 'YYYY-MM-dd HH:mm:ss') as timestamp, measurement 
FROM "AwsDataCatalog"."data-export"."time-series"
WHERE product = '$product' AND ($__timeFilter(timestamp_str, 'YYYY-MM-dd HH:mm:ss'));

Resources and troubleshooting

Here are some other resources for referencing and troubleshooting date and time issues with Athena:

Update 2025-03-22: Added athena tag.


Related ramblings