prestoamazon-athena

How to create a reference to a parsed field in a query in AWS Athena?


In this query, the time field is parsed with parse_datetime 2 times:

SELECT
  date_trunc('HOUR', parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')),
  *
FROM
  logs
WHERE
  parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') BETWEEN timestamp '2020-05-14 19:00' AND timestamp '2020-05-14 23:59'

I tried this, but it didn't work:

SELECT
  parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') as parsed_time,
  date_trunc('HOUR', parsed_time),
  *
FROM
  logs
WHERE
  parsed_time BETWEEN timestamp '2020-05-14 19:00' AND timestamp '2020-05-14 23:59'

It is possible to parse time only once?


Solution

  • Per SQL specification, WHERE clause cannot refer to the projections in the SELECT clause, it can only refer to columns provided by the FROM.

    SELECT *
    FROM (
      SELECT parse_datetime(....) as parsed_time, ...
      FROM ...
    )
    WHERE parsed_time BETWEEN ...