Mixed date formats in the table, how to normalize it

Question & Answer
,
1

I am trying to create an analysis in quicksight using a function of time.

But the problem is, the field which I am using has date in string format as follows: Most of the rows have this format: [“2018-11-30T19:39:14+00:00”] and few of the rows have this format: 2022-08-18 20:54:18

I have to convert them all so that I can represent them as a function of time aggregated by month.

I tried using parse method but it returns most of the rows as null

2

This article lists date formats supported depending on the backend. parseDate - Amazon QuickSight

I believe +00:00 at the end specifies time zone and there are no formats with timezone listed for the parseDate function.

If I were you, I would first define intermediate calculated field that operates on the string to replace letter ‘T’ in the middle with space and removes +00:00 (If the numbers after + can be non-zero, calculation becomes more complex since you need to adjust time zone).

After replacing ‘T’ and removing everything after +, you should be able to use parseDate function to convert all strings to the date data type.

1 Like
3

Hi @learner

Did @Tatyana_Yakushev solution answer your question?
If so, please help the community out by marking this answer as "Solution!

4

Hi @learner,
I am marking @Tatyana_Yakushev reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!