CODEWITHSUNDEEP
unixhadoophiveapache-pighadoop2
user2928137
user2928137

Reputation: 59

hive time_stamp convert into UTC with time_offset in UTC

I have 2 columns: time_stamp and time_offset. Both are STRING data type. How can we convert one column values into UTC with the help of second column which is in UTC? Is their any hive or from unix solution to convert time_stamp column into UTC?

  hive> select time_stamp from table1 limit 2;
    OK
    20170717-22:31:57.348
    20170719-21:10:15.393

[yyyymmdd-hh:mm:ss.msc] this column is in local time

    hive> select time_offset from table1 limit 2;
    OK
    -05:00
    +05:00

[‘+hh:mm’ or ‘-hh:mm’ ] this column is in UTC

Upvotes: 1

Views: 1899

Answers (1)

Chris Nauroth
Chris Nauroth

Reputation: 9844

You can use the Hive Date Functions unix_timestamp and from_unixtime to perform the conversion.

Code

WITH table1 AS (
    SELECT '20170717-22:31:57.348' AS time_stamp, '-05:00' AS time_offset UNION ALL
    SELECT '20170719-21:10:15.393' AS time_stamp, '+05:00' AS time_offset
)
SELECT
    time_stamp,
    time_offset,
    unix_timestamp(concat(time_stamp, ' ', time_offset), 'yyyyMMdd-HH:mm:ss.SSS X') AS unix_timestamp_with_offset,
    from_unixtime(unix_timestamp(concat(time_stamp, ' ', time_offset), 'yyyyMMdd-HH:mm:ss.SSS X'), 'yyyyMMdd-HH:mm:ss.SSS') AS string_timestamp_with_offset
FROM table1
;

Result Set

+------------------------+--------------+-----------------------------+-------------------------------+--+
|       time_stamp       | time_offset  | unix_timestamp_with_offset  | string_timestamp_with_offset  |
+------------------------+--------------+-----------------------------+-------------------------------+--+
| 20170717-22:31:57.348  | -05:00       | 1500348717                  | 20170717-20:31:57.000         |
| 20170719-21:10:15.393  | +05:00       | 1500480615                  | 20170719-09:10:15.000         |
+------------------------+--------------+-----------------------------+-------------------------------+--+

Explanation

unix_timestamp can accept an optional format string in the same syntax as Java SimpleDateFormat. I am guessing that your offsets are using the ISO 8601 syntax, so let's use the X format specifier. Then, we can use the concat String Operator to combine time_stamp and time_offset before passing to unix_timestamp.

The unix_timestamp function results in a numeric timestamp specified as seconds since epoch. To convert that back to a string representation, we can pass the result obtained from unix_timestamp through from_unixtime, this time specifying our original format specifier.

(Please do test thoroughly to make sure the results are making sense in your environment. Time zone math can be tricky.)

Upvotes: 2

Related Questions