在Hive中将Maturity_Date列不同格式时间戳统一转换为MM/dd/yyyy HH:mm:ss格式

需求说明 #

将Hive表中Maturity_Date列的多种格式时间戳，统一转换为MM/dd/yyyy HH:mm:ss格式，当前列取值示例如下：

• 3/22/2022 0:00:00
• 11-08-21 0:00
• 09-07-21
• 10/27/2023 0:00:00

实现逻辑 #

用coalesce函数按优先级匹配不同时间格式的解析规则，先将字符串转成unix时间戳，再通过from_unixtime格式化为目标格式，缺失的时分秒默认补00:00:00。

实现代码 #

SELECT 
  from_unixtime(
    coalesce(
      -- 匹配 月/日/四位年 时分秒格式（适配单/双位月份、日期、小时）
      unix_timestamp(Maturity_Date, 'M/d/yyyy H:mm:ss'),
      -- 匹配 月-日-两位年 时分格式
      unix_timestamp(Maturity_Date, 'MM-dd-yy H:mm'),
      -- 匹配 月-日-两位年 仅日期格式
      unix_timestamp(Maturity_Date, 'MM-dd-yy')
    ), 
    'MM/dd/yyyy HH:mm:ss'
  ) AS Maturity_Date_formatted
FROM 替换为你的实际表名;

注意事项 #

• 若11-08-21这类短横线格式的规则是日-月-年而非月-日-年，将对应pattern里的MM-dd-yy替换为dd-MM-yy即可
• 如有其他未覆盖的时间格式，按相同规则新增pattern参数到coalesce的参数列表中即可
• 若需要12小时制的时间展示，将目标格式中的HH替换为hh

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