Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

presto

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.

like image 687
EvilPuppetMaster Avatar asked Feb 06 '18 00:02

EvilPuppetMaster


People also ask

How do I cast a timestamp to date in Presto?

You can convert timestamp to date with cast(col as date) or date(col) .

Is timestamp stored in UTC?

For timestamp with time zone , the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT ). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.


1 Answers

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);
like image 56
Jason Capriotti Avatar answered Oct 30 '22 12:10

Jason Capriotti