Unable to format dates

Question & Answer
1

Hi,

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.

image
image824×319 39.8 KB

Can someone please tell me what I’m doing wrong?

Thanks

Damien

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”.

2

Hi @damien,

Can you go to Format → More formatting options…? From there, you should see a Time section to display the time.

image
image2206×1440 299 KB

1 Like
3

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:
“2023-01-27T22:25:43:063Z”
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?

Thanks!

4

Hi Damien,

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?

1 Like
5

Hi @Imran_Burki,

Thank you - setting aggregation to a minute fixed it. Completely my fault!! I didn’t realize that was there.

thanks for your help.

Damien

2 Likes