AWS Athena: How to Generate a Timeline of Timestamps as Records

Select Language:

If you want to generate a list of timestamps that appear as individual records in Amazon Athena, you should combine the sequence() function with the UNNEST() clause. The problem many people run into is trying to pass subqueries directly into the sequence() function, which isn’t allowed.

Here’s a simple way to do it that works:

First, create a Common Table Expression (CTE) to find the oldest and newest datetimes in your table. This gives you the range you need to generate timestamps.

Next, use the sequence() function to generate timestamps at regular intervals—say, every 5 minutes—between that minimum and maximum date. Then, apply UNNEST() to convert the list of timestamps into individual rows, so each timestamp shows up as a separate record.

Here’s what the SQL looks like:

sql
WITH date_range AS (
SELECT
MIN(datetime) AS min_date,
MAX(datetime) AS max_date
FROM your_table
)
SELECT
date_value
FROM
date_range,
UNNEST(sequence(
CAST(min_date AS timestamp),
CAST(max_date AS timestamp),
INTERVAL ‘5’ MINUTE
)) AS t(date_value)

This method creates a table where every row is a timestamp spaced five minutes apart, covering the full range from your earliest to latest datetime. You can then join this list with other tables or incorporate it into larger queries, making it easy to align data over specific time intervals.

If you’re planning to link these timestamps with other data, just include this as a subquery or a Common Table Expression (CTE) within your larger SQL statement. It’s a flexible way to generate recurring time points for analysis or reporting.