Search code examples
sqlprestotrino

how to convert TIMESTAMP WITH TIMEZONE to TIMESTAMP but keeping local time in SQL

I wonder how such conversion can be done in SQL, e.g.

2020-07-03 19:47:51.494 America/Los_Angeles => 2020-07-03 19:47:51.494

Note the input data type TIMESTAMP WITH TIMEZONE and the output type is TIMESTAMP.

In particular, I'm using prestosql from https://prestosql.io/.

Solution

  • Hmmm . . . a brute force way is to convert to a string and then back to a timestamp:

    date_parse(format_datetime(datecol, '%Y-%m-%d %H:%i:%s'), '%Y-%m-%d %H:%i:%s')

    Note that this changes the meaning of the value in the column. A timestamp with timezone is really a UTC value that is offset for display purposes. I don't recommend doing this in general. But I have had to do similar operations when local times were moved into "timestamp with timezone" values in a database -- but in the wrong timezone.