Presto: Cast timestamp w/TZ to plain timestamp WITHOUT converting to UTC

Question

This query in Presto:

select *, 
  cast(ts_w_tz as timestamp) as ts, 
  cast(substr(cast(ts_w_tz as varchar), 1, 23) as timestamp) as local_ts_workaround 
from (select timestamp '2018-02-06 23:00:00.000 Australia/Melbourne' as ts_w_tz);

Returns:

                   ts_w_tz                   |           ts            |   local_ts_workaround   
---------------------------------------------+-------------------------+-------------------------
 2018-02-06 23:00:00.000 Australia/Melbourne | 2018-02-06 12:00:00.000 | 2018-02-06 23:00:00.000

As you can see, the act of casting the timestamp with timezone to a timestamp has resulted in the timestamp being converted back to UTC time (eg ts). IMO the correct behaviour should be to return the 'wall reading' of the timestamp, as per local_ts_workaround.

I realise there are many posts about how Presto's handling of this is wrong and doesn't conform to the SQL standard, and that there is a fix in the works. But in the meantime this is a major pain since the effect is that there appears to be no built in way to get a localised timestamp withOUT timezone (as per local_ts_workaround).

Obviously, I have the string conversion workaround for now, but this seems horrible. I am wondering if anyone has a better workaround or can point out something that I have missed?

Thanks.

Answer 1

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_workaround
from (select timestamp '2018-02-06 23:00:00.000 Australia/Melbourne' as ts_w_tz);