如何将UTC格式时间字符串正确存入Hive的Timestamp类型列

问题原因 #

Hive原生timestamp类型默认仅支持不带时区后缀的yyyy-MM-dd HH:mm:ss[.SSS]格式字符串自动转换，你提供的字符串携带UTC时区标识，直接插入会触发格式解析错误，或因时区偏移导致存储值不符合预期。

正确转换方法 #

方法1：使用时区转换函数显式转换 #

通过unix_timestamp指定输入格式与时区，再转换为timestamp类型，SQL示例如下：

-- 直接在插入语句中嵌入转换逻辑
INSERT INTO 你的表名 (timestamp列名) 
VALUES (
  to_utc_timestamp(
    unix_timestamp('2021-11-03 16:57:10.842 UTC', 'yyyy-MM-dd HH:mm:ss.SSS z') * 1000,
    'UTC'
  )
);

• 参数说明：
  • unix_timestamp的第二个参数'yyyy-MM-dd HH:mm:ss.SSS z'里的z代表匹配时区标识（此处的UTC），返回对应时间的秒级时间戳
  • 乘以1000转换为毫秒级时间戳后传入to_utc_timestamp，指定时区为UTC，避免Hive默认使用集群本地时区导致偏移

方法2：临时关闭时区自动转换（针对全量批量插入场景） #

如果批量插入的所有时间字符串都是UTC时区，可以临时调整会话参数，再执行插入：

-- 会话级别设置Hive使用UTC时区，避免本地时区转换
SET hive.session.time.zone = 'UTC';

-- 此时可以直接用字符串转timestamp，无需额外函数
INSERT INTO 你的表名 (timestamp列名) 
VALUES (cast(regexp_replace('2021-11-03 16:57:10.842 UTC', ' UTC$', '') as timestamp));

• 这里先用regexp_replace去掉末尾的UTC后缀，再做类型转换，配合会话时区设置可以保证存储值和预期完全一致

验证方法 #

插入完成后可以执行查询验证值是否正确：

SELECT date_format(timestamp列名, 'yyyy-MM-dd HH:mm:ss.SSS z') FROM 你的表名;

返回结果如果为2021-11-03 16:57:10.842 UTC即为存储正确。

内容的提问来源于stack exchange，提问作者Nikhil Thomas