unix - 配置单元 time_stamp 转换为 UTC 与 time_offset in UTC

Question

我有 2 列:time_stamp 和 time_offset。两者都是 STRING 数据类型。我们如何借助 UTC 中的第二列将一列值转换为 UTC？他们是将 time_stamp 列转换为 UTC 的任何配置单元还是来自 unix 的解决方案？

  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

Answer 1

您可以使用 Hive Date Functions unix_timestamp 和 from_unixtime 执行转换。

代码

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
;

结果集

+------------------------+--------------+-----------------------------+-------------------------------+--+
|       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         |
+------------------------+--------------+-----------------------------+-------------------------------+--+

解释

unix_timestamp 可以接受与 Java 语法相同的可选格式字符串 SimpleDateFormat .我猜你的偏移量正在使用 ISO 8601语法，所以让我们使用 X 格式说明符。然后，我们可以使用 concat String Operator在传递给 unix_timestamp 之前组合 time_stamp 和 time_offset。

unix_timestamp 函数生成指定为自纪元以来的秒数的数字时间戳。要将其转换回字符串表示形式，我们可以将从 unix_timestamp 获得的结果传递给 from_unixtime，这次指定我们的原始格式说明符。

(请进行彻底测试以确保结果在您的环境中有意义。时区数学可能很棘手。)