Convert timestamp from UTC to America/Los_Angeles

[court...@gmail.com]

I have a column that is a timestamp. Its value is a UTC timestamp:

2014-02-28 08:09:58:000

I now want to use Presto to read this field and convert to America/Los_Angeles. Similar to the Hive function from_utc_timestamp. Is there a way in Presto do this? None of the functionality in Presto we have tried seems to convert this way.

Converting the above value to UTC seems to work with the AT TIME ZONE 'UTC' but does not work when I put in 'America/Los_Angeles'. It just keeps the value the same.

It must be because my local system is set to America/Los_Angeles so it does not think there is anything to do.

[Rickman, Brian T]

Since the column is timestamp rather than 'timestamp with timezone' Presto assumes the value is in the server's local timezone.

One solution is to set the Presto server's timezone to UTC and let it convert the timestamp column to the client's timezone automatically.

Another option, if you know the difference between the stored timezone and your local timezone, is to convert the value using interval:

presto:default> select * from test_timestamp;
c1
-------------------------
2014-02-28 08:09:58.000
(1 row)

Query 20170209_143342_00014_shgr3, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:01 [1 rows, 157B] [1 rows/s, 189B/s]

presto:default> select c1 - interval '8' hour from test_timestamp;
_col0
-------------------------
2014-02-28 00:09:58.000
(1 row)

Query 20170209_143348_00015_shgr3, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:01 [1 rows, 157B] [0 rows/s, 141B/s]

--
You received this message because you are subscribed to the Google Groups "Presto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to presto-users...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vijay Lakshminarayanan]

Have you looked at DATE_FORMAT or FORMAT_DATETIME? [1]

They'll change the type to varchar but since you presumably need it
only at the time of display you should be fine.

[1] https://prestodb.io/docs/current/functions/datetime.html

[Courtney Smith]

Thanks for the replies.  I thought interval -8 would work, but I would like to take in to account day light savings when I convert back from the UTC timestamp to America/Los_Angeles. The Hive function from_utc_timestamp(my_time,'PST') is what I am really looking to get in Presto since it appears to take PDT into account.  I see someone has created a ported UDF that would work.  So I will see if that works.  https://github.com/qubole/presto-udfs.

There seems to be lots of issues we are having maintaining parity between Hive and Presto, especially since all our data assets were created using Hive and now want users to analyze using Presto.

[Piotr Findeisen]

Hi Courtney,

I'm not sure this helps you, however there are some issues around timestamps and time zones in Presto (https://github.com/prestodb/presto/issues/7122).

This is being worked on. From what I heard, the work uncovered several nuisances not described in the issue.

Best rehards,

Piotr

--

[Courtney Smith]

I found that this workaround seems to work.  Its messy but it converts the timestamp for both PST and PDT dates.

cast(concat(cast(<column_ts> as varchar),' UTC') as timestamp with time zone) AT TIME ZONE 'America/Los_Angeles' as column_ts_PST,

On Wednesday, February 8, 2017 at 4:23:17 PM UTC-8, Courtney Smith wrote:

[fied...@gmail.com]

Just to clarify, I would like to point out that as far as I know all TIMESTAMP (W/O TIME ZONE) operations are currently done based on session timezone which is derived from CLIENT not a server.
You can manipulate that timezone for example by staring CLI with -Duser.timezone="...." switch.
For that reason setting session timezone to UTC may be another workaround for your problem.

And yes, there is currently ongoing work (#7122) on fixing TIMESTAMP semantic in Presto so that it will match SQL standard.