Presto: Cast timestamp w/TZ to plain timestamp WITHOUT converting to UTC
It seems like there's no great solution, but building off of the previous answer, I like this a little better... see the date_format_workaround column:
select *, cast(from_iso8601_timestamp(date_format(ts_w_tz, '%Y-%m-%dT%H:%i:%s')) as timestamp) as date_format_workaround, cast(ts_w_tz as timestamp) as ts, cast(substr(cast(ts_w_tz as varchar), 1, 23) as timestamp) as local_ts_workaroundfrom (select timestamp '2018-02-06 23:00:00.000 Australia/Melbourne' as ts_w_tz);