Convert string with timezone to unix timestamp

Question

I'm trying to convert a string with a specific timezone (not UTC) to an unix timestamp.

Conversion from strings in UTC works pretty well with unix_timestamp function:

hive> select unix_timestamp("2018-12-31 23:59:59 UTC", "yyyy-MM-dd HH:mm:ss z") as unixtime;
unixtime
1546300799

However, when I simply change the timezone (to another valid TZ name) it doesn't work:

hive> select unix_timestamp("2018-12-31 23:59:59 America/Sao_Paulo", "yyyy-MM-dd HH:mm:ss z") as unixtime;
unixtime
NULL

Any suggestions?

Possible duplicate of [How to convert a Date String from UTC to Specific TimeZone in HIVE?](https://stackoverflow.com/questions/28508640/how-to-convert-a-date-string-from-utc-to-specific-timezone-in-hive) — Tamara Koliada, Jan 02 '19 at 19:55
It possibly duplicates of https://stackoverflow.com/q/28508640/10455534 - check maybe you can find answers there — Tamara Koliada, Jan 02 '19 at 19:56
Yes, its similar, but not exactly the same thing. Function `unix_timestamp` is easy to use when we already have the string in UTC (as in the first example). So convert UTC string to other timezones is straightforward once we get unix timestamp. The function seems to not work in the opposite way, i.e. from a string in a given timezone to a unix timestamp. — Victor Mayrink, Jan 02 '19 at 20:19

score 0 · Answer 1 · answered Jan 02 '19 at 23:19

Your problem is the timezone itself, my understanding is that there is no direct way to do this using java cannonical id ("America/Sao_Paulo"). The correct format for the date that you are looking for (in full) should be

select unix_timestamp("2018-12-31 23:59:59 Brasilia Summer Time", "yyyy-MM-dd HH:mm:ss zzzz") as unixtime;

Convert string with timezone to unix timestamp

1 Answers1