Search code examples
sqltimestampamazon-quicksightamazon-athena

Quicksight data from Athena for correct TimeStamp

I am trying to use date time in AWS quicksight from Athena and parse it out from each file name (paht) that has a format like this '2022-06-02 19:26:48.491730xxxxx'

Using this

date(date_format(AT_TIMEZONE(cast (regexp_extract("$path", '\w{4}-\w{2}-\w{2} \w{2}:\w{2}:\w{2}.w{6}') as timestamp),'America/Los_Angeles'),'yyyy-MM-dd HH:mm:ss:SSSSSS'))  AS time_stamp,

I get null

Solution

  • This should work for you:

    date_parse(date_format(AT_TIMEZONE(cast (regexp_extract("$path", '\w{4}-\w{2}-\w{2} \w{2}:\w{2}:\w{2}') as timestamp),'America/Los_Angeles'),'%Y-%m-%d %H:%i:%s'),'%Y-%m-%d %H:%i:%s')  AS time_stamp