如何将Hive中CST时区string类型日期转换为yyyy-MM-dd格式

报错原因 #

to_date() 函数默认仅支持 yyyy-MM-dd、yyyy-MM-dd HH:mm:ss 这类标准格式的字符串输入，你传入的是自定义格式的CST时区字符串，无法被默认规则解析，因此返回null。

正确实现方案 #

适用版本：Hive 2.1.0及以上 #

直接使用带自定义格式与时区参数的to_timestamp函数解析后转日期即可，你提供的日期字符串对应的格式模板为 dd-MMM-yy hh.mm.ss a z，示例代码如下：

SELECT 
  to_date(
    to_timestamp(Start_date, "dd-MMM-yy hh.mm.ss a z", "CST")
  ) AS formatted_start_date
FROM your_table_name;

参数说明：

• 第一个参数：待转换的原始字符串字段
• 第二个参数：原始字符串的格式匹配模板，完全对应你的输入规则：
  • dd：两位日期
  • MMM：英文缩写月份
  • yy：两位年份
  • hh.mm.ss：12小时制的时分秒（用.分隔）
  • a：匹配AM/PM标识
  • z：匹配时区标识
• 第三个参数：指定解析时使用的时区为CST，避免集群默认时区不同导致日期偏移。

低版本Hive兼容方案 #

如果你的Hive版本不支持三参数的to_timestamp，可以先去掉末尾的CST标识后再解析：

SELECT 
  to_date(
    to_timestamp(
      regexp_replace(Start_date, " CST$", ""), 
      "dd-MMM-yy hh.mm.ss a"
    )
  ) AS formatted_start_date
FROM your_table_name;

验证示例 #

用你给出的示例值测试，运行代码：

SELECT to_date(to_timestamp("02-JUN-22 11.13.22 AM CST", "dd-MMM-yy hh.mm.ss a z", "CST"));

输出结果为：2022-06-02，完全符合预期。

注意事项 #

• 若运行时出现null返回，可先执行set hive.locale.default=en_US;设置英文locale，避免中文环境下无法识别JUN这类英文月份缩写
• 模板必须和原始字符串完全匹配，不能随意修改分隔符、格式符规则

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