March 2, 2023, 8:30pm
I am using Athena to feed data to Quicksight.
In my Athena query, I’m casting an ISO string to a Timestamp like this.
cast(from_iso8601_timestamp(dct.start) as timestamp) startdate2
This appears to ‘work’ in that, in the QuickSight DataSet, it shows up as a date.
When I display it in a grid though in QuickSight, it only shows the date.
This is despite me setting the format to include the time.
Can someone please tell me what I’m doing wrong?
PS - I had more images in here but the bulletin board wouldn’t let me post more than one because I’m a “new user”.
Can you go to
Format → More formatting options…? From there, you should see a Time section to display the time.
March 3, 2023, 12:24am
Hi Imran, thanks for getting back to me.
My bad, I didn’t notice the ‘more formatting options’.
I’m afraid I’m still having issues though.
When I view the data in the QuickSight DataSet, a sample reads:
The little icon next to the data is showing “date” (as opposed to string).
When I select a date format that includes time, for example
“d-MMM-YYYY h:mm:ss A”, QuickSight displays the time as 12:00:00 AM.
Selection of other time formats seem to apply, but still return similarly incorrect results.
Eg - ‘hA’ will yield ‘12AM’.
I would have thought that, given the field was typed as a date, this would work.
Any further suggestions?
Can you please try to go to the
Custom section in the formatting pane and specify a custom time such as DD-MMM-YYYY HH:mm:ss? Does that work? Also, in the Aggregation, what happens if you select minute?
March 5, 2023, 9:09pm
Thank you - setting aggregation to a minute fixed it. Completely my fault!! I didn’t realize that was there.
thanks for your help.