1

I changed Looker database connection from fast access to Athena. However the fast access is using spark SQL, which is no longer work in Athena. The sql: from_unixtime(unix_timestamp(${TABLE}.dt,"yyyyMMdd")) works with fast access but not in Athena. So I would like to find the equivalent function for this in AWS athena, which return the same result in same data type.

Qian
  • 13
  • 3

1 Answers1

3

It's not clear from your question exactly what data type you want, but I'm guessing you want a TIMESTAMP. The input looks like it's a compact ISO date, e.g. "20190204".

To parse that input into a TIMESTAMP in Athena you can do date_parse(${TABLE}.dt, '%Y%m%d'). That function returns a TIMESTAMP. You can also use parse_datetime(${TABLE}.dt, 'yyyyMMdd') if you prefer Java/JodaTime format strings.

You can find all the date and time functions supported by the version of Presto that Athena currently uses here: https://prestodb.github.io/docs/0.172/functions/datetime.html

Theo
  • 131,503
  • 21
  • 160
  • 205