1

I have a string like so: "2014-09-02T03:01:09.8093664Z", and Im trying to convert it into local timezone. I tried from_utc_timestamp(eventTime, 'GMT'), from_utc_timestamp(eventTime, "PDT"), but Hive just returns error: 

Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{"_col0":"2014-09-02T03:01:09.8093664Z",
.
.
.
    ... 7 more
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: Error evaluating Converting field _col0 from UTC to timezone: 'PDT'

Am I doing something wrong here?

I searched stackoverflow and did not find a solution to this problem (Local Time Convert To UTC Time In Hive is related but doesn't solve the problem)

Leistungsabfall
  • 6,368
  • 7
  • 33
  • 41
sargeMonkey
  • 606
  • 1
  • 7
  • 23
  • I was suggested a hack to remove the 'T' from the string and Hive should start recognizing it as datetime. Is there any other way of converting the above UTC to local? – sargeMonkey Sep 24 '14 at 19:50

3 Answers3

4

from_unixtime(UNIX_TIMESTAMP("2014-09-02T03:01:09Z", "yyyy-MM-dd'T'HH:mm:ss'Z' "),"yyyy-MM-dd HH:mm:ss")

its conver to 2014-09-02 03:01:09

Mansur A
  • 41
  • 2
0

An usesful way to solve this problem is creating an UDF function to make this operation. This new one could be specific for this case or more generic adapted to more datetime format conversions. You could read below some benefits:

  • Makes your hive query more readable
  • Avoid duplicated code if you need this operation in other queries
  • Makes more scalable your systems because you can update this method whenever you want
  • Delegate the complex operations to Java code, and consequently you will be able to test those complex parts.

Could you read more about how to create a customized UDF here.

If you need to know how to implement this method in Java I've found in Starckoverflow a post that explains you a way to that, here you have the entry.

Community
  • 1
  • 1
Miguel
  • 1,361
  • 1
  • 13
  • 24
0

You must first extract the time and date string in the proper format before you convert it to GMT. This requires the following format 'yyyy-MM-dd HH:mm:ss'.

Use a regexp_replace to extract the string and then pipe that to the from_utc_timestamp function like this:

select from_utc_timestamp(regexp_replace(event_time,'(\^\\d{4}-\\d{2}-\\d{2})T(\\\d{2}:\\d{2}:\\d{2}).*','$1 $2),'GMT') from my table;

Your output is then: 2014-09-01 03:01:09

Good luck!

invoketheshell
  • 3,819
  • 2
  • 20
  • 35