企业官网建设流程全解析

1. 为什么日期函数值得单独拎出来讲

但凡做过一段时间数据开发的人，心里都有一本账：SQL写得好不好，一半看关联逻辑，另一半就看日期处理。尤其是用Hive做离线数仓的场景，几乎每张事实表都带分区字段，每个指标都要按天、按周、按月汇总，每次跑批都要算时间窗口。日期函数用得不熟，轻则SQL写得又臭又长，重则数据口径出错，下游报表全盘皆输。

我见过太多这样的情况：一个简单的“最近7天活跃用户”需求，有人写出来的SQL嵌套了四层子查询，而熟悉日期函数的人两行就搞定。差距不在智商，在于对函数库的熟悉程度。Hive的日期函数不算多，但坑不少，尤其是版本差异、时区问题、返回类型这些细节，文档里往往一笔带过，真正踩过才知道疼。

这篇内容适合三类人：刚接触Hive SQL的数据分析新手、从MySQL转过来需要适应Hive语法差异的开发人员、以及想系统梳理日期处理套路的数仓工程师。我会把Hive日期函数按使用场景拆开讲，每个函数配上实际案例和踩坑记录，尽量做到看完就能直接抄作业。核心关键词就一个：Hive日期函数，围绕它把日期解析、格式化、计算、截取、转换这几条线全部串起来。

2. 日期函数整体分类与选型思路

2.1 按功能维度拆解日期函数家族

Hive的日期函数大致可以分成五类，我习惯按“从字符串到字符串”的完整链路来理解：

• 解析类：把字符串变成日期或时间戳，比如to_date、unix_timestamp、from_unixtime
• 格式化类：把日期变成指定格式的字符串，比如date_format
• 计算类：日期加减、差值计算，比如date_add、date_sub、datediff、months_between
• 截取类：取年、月、日、周等部分，比如year、month、day、weekofyear
• 转换类：日期和时间戳互转，比如unix_timestamp和from_unixtime的配合使用

这五类不是孤立的，实际写SQL时经常串在一起用。比如从日志里提取日期，先to_date解析，再date_sub算前一天，最后date_format输出成分区格式。

2.2 选型时最容易纠结的几个点

第一个纠结：用to_date还是substr？如果原始字段是标准的yyyy-MM-dd HH:mm:ss格式，to_date和substr(str, 1, 10)结果一样。但to_date更语义化，而且能处理一些非标准格式（比如yyyy/MM/dd），substr则完全依赖字符串位置。我的建议是：只要字段是日期语义，就用to_date，别图省事用字符串截取，后期维护会骂人。

第二个纠结：时间戳用unix_timestamp还是to_unix_timestamp？这两个函数在多数场景下可以互换，但to_unix_timestamp支持指定格式，更灵活。不过要注意，Hive 2.0之前to_unix_timestamp的行为有差异，如果集群版本较老，建议统一用unix_timestamp加格式参数。

第三个纠结：日期加减用date_add还是date_sub还是直接+ interval？Hive支持date_add(start_date, days)和date_sub(start_date, days)，也支持start_date + interval 1 day这种写法。实测下来，date_add和date_sub在大多数版本上更稳定，interval写法在部分老版本上有兼容性问题。另外注意，date_add的第二个参数可以是负数，效果等于date_sub。

提示：Hive版本差异是日期函数最大的坑源。1.x、2.x、3.x在日期处理上有不少行为变化，尤其是unix_timestamp的返回类型和date_format的格式串支持。写SQL前先确认集群版本，别拿网上抄来的代码直接跑。

3. 核心日期函数逐个拆解与实操要点

3.1 解析与格式化：to_date、date_format、from_unixtime

to_date是最常用的解析函数，作用是把字符串或时间戳转成yyyy-MM-dd格式的日期。它的签名是to_date(string timestamp)，返回值是字符串类型（注意，Hive里日期本质上是字符串，不是独立的日期类型）。

select to_date('2024-01-15 10:30:00'); -- 结果：2024-01-15 select to_date('2024/01/15'); -- 结果：2024-01-15 select to_date('20240115'); -- 结果：NULL，不支持无分隔符格式

这里有个坑：to_date对格式的容忍度有限，yyyyMMdd这种紧凑格式它不认，得先用from_unixtime或unix_timestamp转一道。另外，如果传入的字符串本身不合法，to_date返回NULL而不是报错，这在数据清洗时是好事，但在排查问题时容易让人懵。

date_format负责把日期按指定格式输出，签名是date_format(date/timestamp/string, fmt)。它支持的格式串和Java的SimpleDateFormat基本一致：

select date_format('2024-01-15 10:30:00', 'yyyyMM'); -- 202401 select date_format('2024-01-15 10:30:00', 'yyyy-MM-dd'); -- 2024-01-15 select date_format('2024-01-15 10:30:00', 'EEEE'); -- Monday select date_format('2024-01-15 10:30:00', 'w'); -- 3（一年中第几周）

实际做数仓分区时，最常见的操作是把日志时间转成yyyyMMdd或yyyy-MM-dd格式写入分区字段。我一般会这样写：

insert overwrite table dwd_user_log partition(dt) select user_id, event_name, date_format(event_time, 'yyyy-MM-dd') as dt from ods_user_log;

from_unixtime是把Unix时间戳（秒级）转成字符串时间，签名是from_unixtime(bigint unixtime, string format)。第二个参数可选，不传则默认输出yyyy-MM-dd HH:mm:ss。

select from_unixtime(1705300200); -- 2024-01-15 10:30:00 select from_unixtime(1705300200, 'yyyyMMdd'); -- 20240115 select from_unixtime(1705300200, 'HH:mm'); -- 10:30

注意：from_unixtime处理的是秒级时间戳，如果原始数据是毫秒级（13位），必须先除以1000。这个坑我踩过不止一次，日志里的时间戳是毫秒，直接丢给from_unixtime会得到离谱的日期，比如跑到几万年后。

3.2 日期计算：date_add、date_sub、datediff、months_between

日期加减是最日常的操作。date_add(start_date, days)返回开始日期加上指定天数后的日期，date_sub则相反。

select date_add('2024-01-15', 7); -- 2024-01-22 select date_sub('2024-01-15', 7); -- 2024-01-08 select date_add('2024-01-15', -7); -- 2024-01-08，等价于date_sub

这两个函数只处理天数，不支持直接加减月或年。要按月加减，得用add_months(start_date, months)：

select add_months('2024-01-15', 1); -- 2024-02-15 select add_months('2024-01-31', 1); -- 2024-02-29，自动处理月末 select add_months('2024-01-15', -3); -- 2023-10-15

add_months有个很实用的特性：如果起始日期是月末，结果也会对齐到目标月的月末。比如1月31日加一个月，得到2月29日（闰年）或2月28日。这个特性在做月度对账时特别有用，不用自己写一堆判断。

datediff(end_date, start_date)返回两个日期之间的天数差，注意参数顺序是“结束日期在前，开始日期在后”：

select datediff('2024-01-22', '2024-01-15'); -- 7 select datediff('2024-01-15', '2024-01-22'); -- -7

months_between(date1, date2)返回两个日期之间相差的月数，结果是浮点数：

select months_between('2024-03-15', '2024-01-15'); -- 2.0 select months_between('2024-02-15', '2024-01-31'); -- 0.48...

这个函数在计算账期、账龄时很有用，但要注意它返回的是小数，如果只需要整数月数，得配合floor或round使用。

3.3 日期截取：year、month、day、weekofyear、dayofweek

截取类函数用起来最简单，但有几个细节容易忽略：

select year('2024-01-15'); -- 2024 select month('2024-01-15'); -- 1 select day('2024-01-15'); -- 15 select hour('2024-01-15 10:30:00'); -- 10 select minute('2024-01-15 10:30:00'); -- 30 select second('2024-01-15 10:30:00'); -- 0

weekofyear返回日期在一年中的周数，dayofweek返回星期几（1=周日，2=周一，...，7=周六）。这里有个文化差异的坑：dayofweek把周日当作一周的第一天，而国内习惯周一是一周的开始。如果要按国内习惯算，得自己转换：

select dayofweek('2024-01-15'); -- 2，表示周一 -- 转成国内习惯：周一=1，周日=7 select if(dayofweek('2024-01-15') = 1, 7, dayofweek('2024-01-15') - 1);

weekofyear的周数计算遵循ISO标准，每年第一周是包含第一个周四的那周。这个规则在做周同比时会影响跨年周的对齐，需要特别注意。

3.4 时间戳转换：unix_timestamp与from_unixtime的配合

unix_timestamp有两个用法：不传参数时返回当前时间戳，传字符串时按指定格式解析：

select unix_timestamp(); -- 当前时间戳 select unix_timestamp('2024-01-15 10:30:00'); -- 1705300200 select unix_timestamp('20240115', 'yyyyMMdd'); -- 1705276800

不传格式参数时，unix_timestamp默认按yyyy-MM-dd HH:mm:ss解析。如果字符串格式不匹配，返回NULL。这里有个版本差异：Hive 1.x 返回的是bigint，Hive 2.x 之后在某些情况下返回decimal，做数值计算时要注意类型转换。

from_unixtime和unix_timestamp是一对反向操作，配合使用可以实现各种日期格式转换：

-- 把yyyyMMdd格式转成yyyy-MM-dd select from_unixtime(unix_timestamp('20240115', 'yyyyMMdd'), 'yyyy-MM-dd'); -- 把日期转成时间戳再取前一天 select from_unixtime(unix_timestamp('2024-01-15', 'yyyy-MM-dd') - 86400, 'yyyy-MM-dd');

第二种写法虽然能实现日期减一天，但不如直接用date_sub直观。我一般只在需要精确到秒的时间计算时才用时间戳做算术。

4. 典型业务场景的完整SQL实现

4.1 场景一：按天分区写入与最近N天汇总

这是数仓里最常见的操作。假设有一张ODS层的日志表，需要按天写入DWD层，并计算最近7天的活跃用户数。

-- 按天分区写入 insert overwrite table dwd_user_active partition(dt) select user_id, date_format(event_time, 'yyyy-MM-dd') as dt from ods_user_log where dt = '${bizdate}' group by user_id, date_format(event_time, 'yyyy-MM-dd'); -- 最近7天活跃用户汇总 select count(distinct user_id) as active_users from dwd_user_active where dt >= date_sub('${bizdate}', 6) and dt <= '${bizdate}';

这里的关键点是：date_sub('${bizdate}', 6)得到的是7天窗口的起始日期，包含当天共7天。很多人会写成date_sub('${bizdate}', 7)，结果变成8天，指标口径就错了。

4.2 场景二：计算用户首次活跃与留存

留存计算是日期函数的综合应用。核心思路是：先找到每个用户的首次活跃日期，再计算后续活跃日期与首次活跃日期的差值。

-- 用户首次活跃日期 with first_active as ( select user_id, min(dt) as first_dt from dwd_user_active group by user_id ), -- 每日活跃用户与首次活跃日期的差值 daily_diff as ( select a.user_id, a.dt, f.first_dt, datediff(a.dt, f.first_dt) as day_diff from dwd_user_active a join first_active f on a.user_id = f.user_id ) -- 计算次日留存、7日留存 select first_dt, count(distinct case when day_diff = 1 then user_id end) as retain_1d, count(distinct case when day_diff = 7 then user_id end) as retain_7d, count(distinct case when day_diff = 0 then user_id end) as new_users from daily_diff group by first_dt;

这个SQL里datediff是核心，它把日期差转成了整数，方便做条件聚合。注意day_diff = 0表示首次活跃当天，也就是新增用户。

4.3 场景三：按周、按月汇总的日期对齐

周汇总和月汇总的难点在于日期对齐。周汇总要确定一周的起始日，月汇总要处理月末。

-- 按周汇总，以周一为一周开始 select date_sub(dt, if(dayofweek(dt) = 1, 6, dayofweek(dt) - 2)) as week_start, count(distinct user_id) as weekly_active from dwd_user_active group by date_sub(dt, if(dayofweek(dt) = 1, 6, dayofweek(dt) - 2)); -- 按月汇总 select date_format(dt, 'yyyy-MM') as month, count(distinct user_id) as monthly_active from dwd_user_active group by date_format(dt, 'yyyy-MM');

周汇总的表达式看起来有点绕，拆开看就清楚了：dayofweek(dt)返回1到7，1是周日。如果今天是周日（1），要减6天到周一；否则减dayofweek - 2天到周一。这样每个日期都能映射到所在周的周一。

4.4 场景四：时间窗口滑动计算

滑动窗口在计算同比、环比时很常用。比如计算每个用户最近30天的活跃天数：

select a.user_id, a.dt, count(distinct b.dt) as active_days_30d from dwd_user_active a join dwd_user_active b on a.user_id = b.user_id and b.dt >= date_sub(a.dt, 29) and b.dt <= a.dt group by a.user_id, a.dt;

这个自关联的写法在数据量大时会很慢，实际生产中更推荐用窗口函数：

select user_id, dt, count(distinct dt) over( partition by user_id order by unix_timestamp(dt, 'yyyy-MM-dd') range between 2592000 preceding and current row ) as active_days_30d from dwd_user_active;

窗口函数的range between用的是秒数，30天等于2592000秒。这种写法比自关联高效得多，但要注意range窗口需要数值类型的排序字段，所以得先用unix_timestamp把日期转成秒数。

5. 常见问题与排查技巧实录

5.1 日期函数返回NULL的几种原因

NULL是日期处理中最常见的“惊喜”。根据我的排查经验，原因主要有这几类：

我一般会在SQL里加一层防御：对关键日期字段用nvl(to_date(col), '1970-01-01')兜底，避免NULL扩散导致整个结果集异常。

5.2 时区问题的处理套路

Hive默认使用集群的时区设置，from_unixtime和unix_timestamp都受时区影响。如果日志里的时间戳是UTC，而集群时区是东八区，直接转换会差8小时。

处理时区问题有两个思路：一是在转换时手动加减偏移量，二是用from_utc_timestamp和to_utc_timestamp函数。

-- 手动加8小时 select from_unixtime(unix_timestamp('2024-01-15 10:30:00') + 8 * 3600); -- 用内置函数 select from_utc_timestamp('2024-01-15 10:30:00', 'Asia/Shanghai'); select to_utc_timestamp('2024-01-15 10:30:00', 'Asia/Shanghai');

提示：from_utc_timestamp和to_utc_timestamp在Hive 1.2之后才支持，老版本只能用偏移量手动算。另外，时区字符串要用标准的区域名，别用缩写。

5.3 跨年周计算的坑

weekofyear在跨年时的行为经常让人困惑。比如2024年1月1日是周一，weekofyear返回1；但2023年12月31日是周日，weekofyear返回52。如果按周汇总时直接group byweekofyear，跨年的周会被拆成两周。

解决办法是用“周起始日期”代替“周数”作为分组依据：

select date_sub(dt, if(dayofweek(dt) = 1, 6, dayofweek(dt) - 2)) as week_start, count(*) as cnt from some_table group by date_sub(dt, if(dayofweek(dt) = 1, 6, dayofweek(dt) - 2));

这样跨年的周会自然对齐到同一个周一，不会出现拆分。

5.4 性能优化：避免在分区字段上套函数

这是一个很容易被忽略的性能问题。如果分区字段是dt，写成where date_format(dt, 'yyyy-MM') = '2024-01'会导致全表扫描，因为函数作用在分区字段上，Hive无法做分区裁剪。

正确的写法是：

-- 不推荐 select * from table where date_format(dt, 'yyyy-MM') = '2024-01'; -- 推荐 select * from table where dt >= '2024-01-01' and dt <= '2024-01-31';

如果确实需要按月过滤，可以用dt >= '2024-01-01' and dt < '2024-02-01'这种范围写法，既能命中分区，又不用套函数。

5.5 日期格式串的兼容性速查

不同Hive版本对格式串的支持有差异，下面这张表是我在实际项目中整理出来的：

如果集群版本较老，建议只用最基础的yyyy、MM、dd、HH、mm、ss这几个，兼容性最好。

6. 几个让我印象深刻的踩坑记录

6.1 毫秒时间戳导致的“穿越”

有一次做用户行为分析，发现部分用户的活跃日期显示为“48921年”。排查了半天，最后发现是日志里的时间戳是13位毫秒级，而from_unixtime按秒级处理，结果直接放大了1000倍。修复方法很简单，除以1000就行：

select from_unixtime(cast(event_time_ms / 1000 as bigint), 'yyyy-MM-dd');

但这个坑提醒我：拿到任何时间戳字段，第一件事是确认位数。10位是秒级，13位是毫秒级，16位是微秒级。位数不对，日期全废。

6.2 date_sub的负数陷阱

date_sub(start_date, days)的第二个参数是正数时表示减天数，但如果传负数，效果等于加天数。这个特性本身没问题，但容易在动态计算时出错。比如：

-- 本意是减7天，但如果offset是负数，就变成加7天 select date_sub('2024-01-15', offset) from some_table;

如果offset来自配置表且可能为负，建议统一用date_add加负数，语义更清晰：

select date_add('2024-01-15', -abs(offset)) from some_table;

6.3 分区字段类型不一致导致的裁剪失效

Hive的分区字段默认是字符串类型，但有些表在创建时把分区字段定义成了int。如果查询时用字符串去过滤，比如where dt = '20240115'，而分区字段是int，Hive会做隐式转换，但可能导致分区裁剪失效。

解决办法是保持分区字段类型一致，或者在查询时显式转换：

where cast(dt as string) = '20240115'

不过更好的做法是建表时就统一用字符串类型存分区，避免后续麻烦。

6.4 月末日期加减的边界情况

add_months处理月末时很智能，但date_add不会。比如1月31日加28天，得到2月28日，但如果目标是2月29日（闰年），就得用add_months：

select date_add('2024-01-31', 28); -- 2024-02-28 select add_months('2024-01-31', 1); -- 2024-02-29

做月度账期计算时，我一般优先用add_months，避免月末对齐问题。

7. 日期函数组合使用的几个高级套路

7.1 用日期函数生成连续日期序列

有时候需要生成一张日期维表，从某天到某天连续不断。Hive没有直接的generate_series函数，但可以用posexplode配合split和space来生成：

select date_add('2024-01-01', pos) as dt from ( select posexplode(split(space(datediff('2024-01-31', '2024-01-01')), ' ')) ) t;

这个写法的原理是：datediff算出天数差，space生成对应数量的空格，split按空格拆成数组，posexplode展开成行号，最后用date_add生成连续日期。虽然有点绕，但在没有维表的情况下很实用。

7.2 计算同比、环比的日期偏移

同比是去年同期，环比是上一个周期。用日期函数可以灵活控制偏移量：

-- 同比：去年同一天 select date_sub(dt, 365) as last_year_same_day from some_table; -- 更精确的同比：用add_months减12个月 select add_months(dt, -12) as last_year_same_day from some_table; -- 环比：上一个月 select add_months(dt, -1) as last_month from some_table;

用add_months做同比比date_sub(dt, 365)更准确，因为闰年有366天，固定减365天在跨闰年时会差一天。

7.3 日期区间展开成多行

有时候一条记录带一个日期区间，需要展开成多行。比如一个活动从1月1日到1月7日，要拆成7行：

select activity_id, date_add(start_date, pos) as dt from activity_table lateral view posexplode(split(space(datediff(end_date, start_date)), ' ')) t as pos, val;

这个套路在活动分析、库存分析里很常用，配合日期函数能省掉很多手工操作。

8. 版本差异与迁移注意事项

Hive 1.x、2.x、3.x在日期函数上的差异主要集中在几个方面：

返回类型：unix_timestamp在1.x返回bigint，2.x之后在某些场景返回decimal。如果下游对类型敏感，建议显式cast。

格式串支持：date_format在1.x对EEEE、D、u等格式串支持不完整，2.x之后才完善。如果从1.x迁移到2.x，原来报错的格式串可能突然能用了，但反过来迁移会出问题。

时区函数：from_utc_timestamp和to_utc_timestamp在1.2之后才引入，老版本没有。

NULL处理：部分函数在1.x对NULL输入直接报错，2.x之后改为返回NULL。这个差异在数据清洗时影响很大。

迁移时的建议是：先在测试环境用相同数据跑一遍，对比结果差异，重点关注NULL值、边界日期（月末、年末、闰年）和时区相关的场景。

9. 我个人的几条实操心得

第一条，日期字段永远用日期函数处理，别用字符串截取。substr(dt, 1, 7)看起来能取月份，但如果数据格式变了，整个SQL就崩了。date_format(dt, 'yyyy-MM')语义清晰，容错性也好。

第二条，分区过滤用范围，别用函数。where dt between '2024-01-01' and '2024-01-31'能命中分区裁剪，where month(dt) = 1只能全表扫。这个习惯能省下大量计算资源。

第三条，时间戳先确认位数再转换。10位秒级、13位毫秒级、16位微秒级，位数不对结果全错。我现在的习惯是拿到时间戳字段先select max(length(cast(ts as string)))看一眼。

第四条，月末和跨年场景单独测试。日期函数的边界问题基本都出在这两个地方。上线前用1月31日、2月28日、12月31日、闰年2月29日这几个日期跑一遍，能避开大部分坑。

第五条，保留一份自己的日期函数速查表。Hive的函数文档虽然全，但查起来慢。我把自己常用的十几个函数和典型用法整理成了一张表，写SQL时直接对照，效率高很多。

最后再分享一个小技巧：如果经常需要把日期转成各种格式，可以封装一个UDF或者用CTE把转换逻辑集中管理，避免在多个子查询里重复写date_format。这样后期改格式时只需要改一处，维护成本低很多。