Why is my standard date format not recognized?

Question & Answer
1

My data is from a DynamoDB table queried via Athena.

The dates are in a standard format, but get imported as strings.

If I try to change the type to a date, I get this message:

image
image686×908 89.9 KB

Using parseDate causes my data set to fail to import, even though the documentation for parseDate says it should convert to this exact format: parseDate - Amazon QuickSight

Using substring with anything (e.g. substring({createdAt}, 0, 1) causes my data set to fail to import, so I can’t construct any other date format manually.

Also I checked and there is no white space in the values.

2

I believe DynamoDB stores dates as strings (in ISO format) and if you are using Athena federation to dynamoDB, then it would see this field as a string.

Hence you need to convert this field in QuickSight. parseDate() returns an ISO date but does not recognize that format for its input string (see link you provided).
Can you try adding a new calculated field as follows:

parseDate(replace(replace({createdAt},'T', ' '),'Z',' '), 'yyyy/MM/dd HH:mm:ss.SSS')
3

It is a bit ridiculous that QuickSight does not support a standard (ISO 8601) format that pretty much every sane system in the world is using these days.

And on top of it, there seems to be no way to force the parser to use the format. I’ve tried every variation under the sun, and it is always coming back with :stop_sign:

2 Likes