AWS Athena: How do I generate a sequence of timestamps as individual records?

To generate a sequence of timestamps as individual records in Athena, you need to use a combination of the sequence() function and UNNEST(). The issue with your current approach is that you're trying to use subqueries directly within the sequence() function parameters, which isn't supported.

Here's how you can accomplish this:

WITH date_range AS (
SELECT 
MIN(datetime) AS min_date,
MAX(datetime) AS max_date
FROM tableA
)
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 query:

1. Creates a CTE (Common Table Expression) called date_range that gets the minimum and maximum datetime values from your table
2. Uses the sequence() function to generate an array of timestamps at 5-minute intervals
3. Uses UNNEST() to convert the array into individual rows
4. Returns each timestamp as a separate record

The result will be a table with individual timestamp records at 5-minute intervals between your min and max datetime values, exactly as you showed in your example.

If you need to join these timestamps with other data, you can now use this as a subquery or CTE in a larger query and perform joins as needed.
Sources
Array functions - Amazon Timestream
Create arrays from subqueries - Amazon Athena