如何在Hive列中插入指定值，将带UTC的时间转为Hive可识别timestamp类型

你可以通过以下两种常用方案实现需求，两种方案都可以保证转换后的字段被Hive正常识别为timestamp类型： #

方案1：插入数据时显式处理（适合表结构已固定的场景） #

先去除时间字符串末尾的UTC后缀，再强转为timestamp类型即可，有两种常用的字符串处理写法：

• 通用替换写法（适合所有带UTC后缀的时间字符串，兼容性好）：
  使用regexp_replace匹配末尾的UTC后缀做替换，SQL示例：

  INSERT INTO target_table
  SELECT
    CAST(regexp_replace(source_time_col, ' UTC$', '') AS TIMESTAMP) AS target_time_col,
    -- 其余字段
  FROM source_table;
  

• 截断写法（适合所有时间字符串长度固定的场景，性能更高）：
  直接截取前19位（即2019-10-01 00:00:00部分）再做类型转换：

  CAST(substr(source_time_col, 1, 19) AS TIMESTAMP) AS target_time_col
  

方案2：建表时指定时间解析格式（适合直接加载原始数据文件的场景） #

如果你的数据是直接存储在HDFS上的原始文件，需要建表时直接自动识别带UTC后缀的时间为timestamp，可以在建表时通过SerDe属性指定时间格式：

CREATE EXTERNAL TABLE your_table (
  time_col TIMESTAMP,
  -- 其余字段定义
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  "timestamp.formats" = "yyyy-MM-dd HH:mm:ss z"
)
LOCATION '/your/hdfs/data/path';

格式字符串中的z代表时区标识，Hive会自动匹配解析UTC这类时区后缀，不需要额外做字符串处理。 #

额外说明 #

如果需要转换后对应东八区时间，可以额外调用from_utc_timestamp函数做时区转换：

from_utc_timestamp(CAST(regexp_replace(source_time_col, ' UTC$', '') AS TIMESTAMP), 'Asia/Shanghai') AS cn_time_col

内容的提问来源于stack exchange，提问作者Gokul Pradeep V