Hive/Hue环境下使用SQL从唯一参考编号中提取转换日期时间戳咨询

前置修正 #

你现有拆分代码存在一处笔误：17位长度取小时的逻辑里误用了caseid字段，需统一改为目标字段AField，避免执行报错。

实现代码 #

你可以直接在现有拆分逻辑的基础上，拼接时间字段后转换为timestamp类型，同时支持输出你要求的DD-MM-YY HHMM格式：

select
AField,
cast((UTC+(60*60*12)*1000)/1000 as TIMESTAMP) as `LocalTime`,
-- 原时间拆分逻辑
case when length(AField) = 18 then substring(AField,1,2)  else substring(AField,1,1) end Days,
case when length(AField) = 18 then substring(AField,10,2) else substring(AField,9,2) end Months,
case when length(AField) = 18 then substring(AField,6,4) else substring(AField,5,4) end years,
case when length(AField) = 18 then substring(AField,12,2) else substring(AField,11,2) end Hours,
case when length(AField) = 18 then substring(AField,14,2) else substring(AField,13,2) end minutes,
-- 转换为timestamp类型字段
to_timestamp(
    concat(
        years, '-', Months, '-', lpad(Days, 2, '0'), ' ', Hours, ':', minutes
    )
) as target_timestamp,
-- 如需输出DD-MM-YY HHMM格式的字符串，新增以下字段
date_format(
    to_timestamp(
        concat(
            years, '-', Months, '-', lpad(Days, 2, '0'), ' ', Hours, ':', minutes
        )
    ),
    'dd-MM-yy HHmm'
) as target_time_str
from table.name

更简化的正则提取实现（可选） #

你可以直接通过正则分组匹配一次性提取所有时间组件，无需写多段length判断逻辑：

select
AField,
cast((UTC+(60*60*12)*1000)/1000 as TIMESTAMP) as `LocalTime`,
-- 正则匹配提取时间后直接转换
to_timestamp(
    concat(
        regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 1), '-',
        regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 2), '-',
        lpad(regexp_extract(AField, '^([0-9]{1,2})[0-9]{3}[0-9]{4}[0-9]{2}[0-9]{2}[0-9]{2}[0-9]{3}$', 1), 2, '0'), ' ',
        regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 3), ':',
        regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 4)
    )
) as target_timestamp,
date_format(
    to_timestamp(
        concat(
            regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 1), '-',
            regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 2), '-',
            lpad(regexp_extract(AField, '^([0-9]{1,2})[0-9]{3}[0-9]{4}[0-9]{2}[0-9]{2}[0-9]{2}[0-9]{3}$', 1), 2, '0'), ' ',
            regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 3), ':',
            regexp_extract(AField, '^[0-9]{1,2}[0-9]{3}([0-9]{4})([0-9]{2})([0-9]{2})([0-9]{2})[0-9]{3}$', 4)
        )
    ),
    'dd-MM-yy HHmm'
) as target_time_str
from table.name

说明 #

• lpad函数用于将1位的日期补上前导0，统一为2位格式，避免时间解析出错
• Hive的to_timestamp函数默认支持yyyy-MM-dd HH:mm格式的字符串转换为timestamp类型
• 若仅需timestamp类型字段，可删除target_time_str相关逻辑，若仅需指定格式的时间字符串，也可直接拼接生成无需中转timestamp

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