Presto: at time zone doesn't work with data from a column. It must be typed

[kindjacket]

Example

select time at time zone 'America/New_York' as time_with_tz
 from (
select
'America/New_York' as timezone,
cast('2018-09-05 14:43:47.199 UTC' as timestamp ) as time
);

returns expected result 2018-09-05 10:43:47.199 America/New_York

but

select time at time zone timezone as time_with_tz
 from (
select
'America/New_York' as timezone,
cast('2018-09-05 14:43:47.199 UTC' as timestamp ) as time
);

returns the following exception

[HY000][100071] [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. line 1:26: no viable alternative at input 'time zone timezone' java.lang.RuntimeException: com.simba.athena.amazonaws.services.athena.model.InvalidRequestException: line 1:26: no viable alternative at input 'time zone timezone' (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: ee646aad-86ab-468b-a54d-78464531f13e)

This means adjusting the timezone dynamically (through a join for example) is impossible.

Using Presto via Athena (Presto version 0.172). If this has been fixed in a future version I will reach out to AWS.

[dain]

The at time zone language construct is limited by spec to a constant, so use the at_timezone(datetime, timezone) function instead.

[kindjacket]

@dain - Amazing! Thank you! It would be nice if this was in the Presto documentation https://prestodb.io/docs/current/functions/datetime.html
Happy to make a PR if the documentation is in this repo