Hi there. I connected QuickSight to two Datasets (tables) using Athena, in the same database.
The problem I’m encountering is that when I “connect” both datasets in QuickSights data prep, by performing a left join between a “primary key” from one table and the “foreign key” on the second one, the data prep displays a preview of the mixed dataset but my column “snapshot”, which is in Date format, seems to be null. I double checked in a Dashboard by creating a dummy table and it’s still null in the visualization part, even though it’s not null in my database. For further info, in my database it’s using TIMESTAMP format.
The issue with your timestamp column appearing as null when joining datasets in QuickSight is likely related to how QuickSight handles timestamp data types from Athena. This is a known problem when working with timestamp columns from Athena-based datasets, particularly with Iceberg tables. See old post here and other Old post here
When previewing data in QuickSight, timestamp columns may appear empty even though they contain values in the original database.
This happens because QuickSight might be interpreting the timestamp format incorrectly or struggling with null values in the timestamp column. Refer old post here and Refer here as well
To fix this issue, you may try the below options:
Check the aggregation settings for your timestamp column in the analysis. Make sure it’s aggregated by “Second” to see the full date/time information.
If your timestamp column contains null values or empty strings, you can use the parseDate function with an ifelse statement to handle these cases properly.
Another approach is to modify your Athena query to cast the timestamp column properly before importing it to QuickSight.
You can use:
CAST(CAST(myTimestamp as varchar) as timestamp) as convTimestamp
If you’re using SPICE mode for your dataset, consider using Direct Query mode instead, as some users have reported that timestamp issues only occur when using SPICE.
For a more permanent solution, you could create a new field in Athena that properly parses problematic timestamp records to Null, then use this field in QuickSight.
By implementing one of these solutions, you may be able to properly display your timestamp data when joining datasets in QuickSight.
Thank you for the welcome message and for your quick response as well
I tried the approaches you mentioned, except the casting one as I’m not using custom SQL to import the data.
I’ll provide some more specific information in case you or someone can see something I’m missing, as I’m very new to QuickSight and Athena as well.
1.- I can guarantee that “snapshots” is a TIMESTAMP type and won’t be null. In fact, I just have two distinct values in the database: 2025-06-03 11:41:28.234000 UTC and 2025-06-02 09:26:56.707000 UTC.
2.- After relating “projects” to “project_files” in the Data prep section, “snapshots” fields are all null (while they should be one of the mentioned above), but surprisingly, my fields “created_at” or “updated_at” have a correct not null value (e.g. 2022-12-07 12:27:21.314000 UTC) as you can see in the next screenshot (even though “snapshots” belongs to table “project_files” and “created_at” and “updated_at” belongs to table “projects”):
3.- As you can also see in the screenshot above, when I apply a LEFT JOIN relation between both tables, the Query mode defaults to SPICE and I don’t see any feature to change it back to Direct query.
4.- In the actual analysis, the values of “snapshot” are as well null, even with any aggregation.
I thought of a weak solution, that would be to change the schema in my database and specify “snapshot” as a VARCHAR instead of a TIMESTAMP, and then converting it in QuickSight using a Calculated field. However, I still find it very odd that “created_at” and “updated_at” are able to be parsed correctly even thought they follow the same format as “snapshot”.
I found my way to solve this… I changed “snapshot” type to String in the Data prep section, and then added a calculated field parseDate(snapshot, "yyyy-MM-dd HH:mm:ss").
However it doesn’t make sense to me that the other Date fields are working without any manipulation…
