Hive SQL 时间转换

在做数仓开发时，时间字段的格式转换几乎是每天都要面对的问题。源系统给的时间可能是 20251225084123​ 这种没有分隔符的字符串，而目标表需要的却是 2025-12-25 08:41:23​ 这种标准格式。本文从原理讲起，把 Hive（同样适用于 Spark SQL）里常见的时间转换方式一次性讲清楚，并给出版本兼容性建议。

一、为什么会有这个问题

很多业务系统（尤其是 SAP 等 ERP 系统同步过来的数据）存储时间字段时习惯用定长数字字符串，比如：

• ​20251225084123​ 表示 2025年12月25日 08:41:23​（14位，格式为 yyyyMMddHHmmss​）
• ​20251225​ 表示 2025年12月25日​（8位，格式为 yyyyMMdd​）

而数仓的 DWD/DWS 层通常要求统一成带分隔符的标准格式，方便下游报表、BI 工具直接使用。这就需要在 ETL 环节做格式转换。

二、原理：以 Unix 时间戳为桥梁

Hive 里最经典的转换方式，是把字符串日期和 Unix 时间戳（1970-01-01 00:00:00 UTC 到某一时刻的秒数）互相转换，作为中间桥梁：

 字符串(任意格式) --unix_timestamp()--> 秒数(bigint) --from_unixtime()--> 字符串(目标格式)

1. unix_timestamp

把字符串按你告诉它的格式解析成秒数：

 select unix_timestamp('20251225084123', 'yyyyMMddHHmmss');
 -- 返回：1766652083

关键：第二个参数必须和字符串的实际格式完全对应，否则解析失败返回 NULL​（不会报错）。

2. from_unixtime

把秒数按目标格式渲染成字符串：

 select from_unixtime(1766652083, 'yyyy-MM-dd HH:mm:ss');
 -- 返回：2025-12-25 08:41:23

不传第二个参数时，默认格式就是 yyyy-MM-dd HH:mm:ss​。

3. 组合使用

 select from_unixtime(
   unix_timestamp('20251225084123', 'yyyyMMddHHmmss'),
   'yyyy-MM-dd HH:mm:ss'
 );
 -- 2025-12-25 08:41:23
 ​
 select from_unixtime(
   unix_timestamp('20251225', 'yyyyMMdd'),
   'yyyy-MM-dd'
 );
 -- 2025-12-25

格式占位符对照表

⚠️ 最容易踩的坑：月份是大写 MM​，分钟是小写 mm​，写反了会导致解析结果错乱甚至变成 NULL​。

三、实战案例

假设有一张订单流水表 ods_order_info​，其中下单时间、发货日期都是源系统同步过来的定长数字字符串，我们要把它清洗进 DWD 层，目标字段类型都是 string​：

 -- 源表结构示例
 -- order_id       string   订单号
 -- user_id        string   用户ID
 -- order_amt      decimal  订单金额
 -- create_time    string   下单时间，格式如 20251225084123
 -- ship_date      string   发货日期，格式如 20251226
 ​
 insert overwrite table dwd_trade.dwd_order_info_df
 select
  t1.order_id                                                                          as order_id
 ,t1.user_id                                                                           as user_id
 ,t1.order_amt                                                                         as order_amt
 ,from_unixtime(unix_timestamp(t1.create_time, 'yyyyMMddHHmmss'), 'yyyy-MM-dd HH:mm:ss') as create_time
 ,from_unixtime(unix_timestamp(t1.ship_date, 'yyyyMMdd'), 'yyyy-MM-dd')                  as ship_date
 ,current_timestamp()                                                                  as etl_time
 from ods_trade.ods_order_info t1
 where t1.dt = '20260728'

转换效果：

四、其他几种转换方式

方式一：substr 拼接

利用定长字符串的特点，直接截取拼接，不走日期解析：

 concat(
   substr(t1.timestamp,1,4), '-', substr(t1.timestamp,5,2), '-', substr(t1.timestamp,7,2), ' ',
   substr(t1.timestamp,9,2), ':', substr(t1.timestamp,11,2), ':', substr(t1.timestamp,13,2)
 ) as mdify_time
 ​
 concat(substr(t1.cpudt,1,4), '-', substr(t1.cpudt,5,2), '-', substr(t1.cpudt,7,2)) as tckt_date_acct

• 优点：性能高，即使是"20250230"这种不存在的日期也能正常拼出来，不会变 NULL
• 缺点：不做合法性校验，脏数据会被悄悄放过

方式二：to_timestamp等函数

Hive 2.1.0 以后支持，写法更符合"现代 SQL"风格：

 date_format(to_timestamp(t1.timestamp, 'yyyyMMddHHmmss'), 'yyyy-MM-dd HH:mm:ss') as mdify_time
 date_format(to_date(t1.cpudt, 'yyyyMMdd'), 'yyyy-MM-dd') as tckt_date_acct

• 优点：可读性更好，是 Spark SQL 官方更推荐的写法
• 缺点：Hive 2.1.0 以下版本不支持带格式参数

方式三：regexp_replace 插入分隔符

 regexp_replace(t1.cpudt, '(\\d{4})(\\d{2})(\\d{2})', '$1-$2-$3') as tckt_date_acct

• 优点：一行搞定
• 缺点：正则分组不直观，同样不做合法性校验

五、四种方式对比

六、版本兼容性建议

如果你的生产环境同时存在 Hive 2.x 和 4.x（甚至更早的版本混用），选函数时优先考虑两个版本交集里最稳的：

• ​unix_timestamp​ / from_unixtime​ 是 Hive 最早期就有的函数，不存在任何版本坑，2.x（哪怕是 2.0.x）和 4.x 都能直接跑，是兼容性最好的首选。
• ​to_timestamp​ / to_date​ 带格式参数的重载版本是 Hive 2.1.0 才引入的，如果集群版本低于 2.1.0，会报错或行为异常，属于版本地雷，多版本混跑环境要谨慎使用。
• ​substr​、concat​、regexp_replace​ 这类字符串函数从来没有版本问题，但要注意它们不做日期合法性校验。

七、数据质量检查

用 unix_timestamp​/to_timestamp​ 这类会做校验的函数时，脏数据会被静默转成 NULL​，容易被忽略。建议先跑检查语句摸底：

 -- 检查 create_time 源字段中格式不合法的数据量
 select count(*) as bad_create_time
 from ods_trade.ods_order_info
 where dt = '20260728'
   and create_time is not null
   and unix_timestamp(create_time, 'yyyyMMddHHmmss') is null;
 ​
 -- 检查 ship_date 源字段中格式不合法的数据量
 select count(*) as bad_ship_date
 from ods_trade.ods_order_info
 where dt = '20260728'
   and ship_date is not null
   and unix_timestamp(ship_date, 'yyyyMMdd') is null;

如果两个查询结果都是 0，说明数据干净，可以放心用带校验的转换方式；如果数量不为 0，需要先确认这批脏数据要不要清洗或单独处理，再决定是否切换成 substr​ 这种不校验的方式。

八、总结