Search code examples
amazon-web-servicesamazon-quicksight

Convert timestamp field to Date String in Amazon QuickSight

I am trying to do customer behavior analysis in AWS Quick Sight. One of the data fields is a timestamp e.g. 20200219215336.

I want to cast this field to a date-time string e.g. 2020-02-19 21:53:36.0 UTC (+00:00).

I tried to change the data type to date in Quick Sight but it is converting it to a wrong date. Something like 2034-01-04T21:12:01.000Z

I also tried using epochDate() function, but it is giving me the same results.

Any ideas on how to convert 20200219215336 to 2020-02-19 21:53:36.0 UTC (+00:00)?

Solution

  • By looking closely at the value, I realized that the value is actually a date-time string with no separator.

    Following calculated filed worked for me in AWS QuickSight

    parseDate(concat(substring({ts},1,4),'-',substring({ts},5,2), '-',substring({ts},7,2),' ',substring({ts},9,2),':',substring({ts},11,2),':',substring({ts},13,2)),'yyyy-MM-dd HH:mm:ss')