3

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)?

Vikas Roy
  • 854
  • 2
  • 10
  • 25

2 Answers2

4

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')
Vikas Roy
  • 854
  • 2
  • 10
  • 25
2

There is a better way of transforming directly the timestamp strings into a date-time:

epochDate({time_value})

Where time_value is the int as time stamp. Watch out for the length of the integer, if too long probably you will need to divide the number.

Reference: https://theblinkything.com/index.php/2018/09/09/quick-tip-using-unix-timestamps-or-epochs-in-quicksight/

Servio Reyes
  • 21
  • 1