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?
Per SQL specification, WHERE
clause cannot refer to the projections in the SELECT
clause, it can only refer to columns provided by the FROM
.
WHERE
clause: WHERE parse_datetime(...) BETWEEN ...
SELECT *
FROM (
SELECT parse_datetime(....) as parsed_time, ...
FROM ...
)
WHERE parsed_time BETWEEN ...