日期函数全家桶，一篇搞定面试和工作

SQL手册第 15 题约 12 分钟读完

本文目录

总结
一、获取当前日期和时间
二、日期加减
三、日期差值
四、日期格式化和解析
五、提取日期部分
六、时间戳和日期互转
七、面试综合题

总结

操作	Hive/Spark	MySQL	PostgreSQL
------	-----------	-------	------------
当前日期	current_date()	CURDATE()	CURRENT_DATE
加N天	DATE_ADD(dt, N)	DATE_ADD(dt, INTERVAL N DAY)	dt + N
加N月	ADD_MONTHS(dt, N)	DATE_ADD(dt, INTERVAL N MONTH)	dt + INTERVAL 'N months'
日期差	DATEDIFF(end, start)	DATEDIFF(end, start)	end - start
格式化	DATE_FORMAT(dt, 'yyyy-MM')	DATE_FORMAT(dt, '%Y-%m')	TO_CHAR(dt, 'YYYY-MM')
时区转换	FROM_UTC_TIMESTAMP()	CONVERT_TZ()	AT TIME ZONE

面试建议：声明你用的引擎。 写日期函数之前先说一句"我用Hive/Spark的语法"，避免面试官按另一个引擎的标准判你错。

明天 DAY 16 聊UNION ALL vs UNION的区别和使用场景，以及怎么用UNION ALL优雅地解决一些看似复杂的问题。

你最常踩的日期函数的坑是什么？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

日期处理大概是SQL里最让人头疼的部分——不是难，是乱。同一个功能，MySQL、Hive、Spark、PostgreSQL的写法都不一样。面试的时候你写了一个MySQL的日期函数，面试官用的是Spark，直接判你语法错误。今天系统梳理一遍，把各引擎的常用日期函数都覆盖到。

一、获取当前日期和时间

-- MySQL
SELECT NOW();              -- 2025-02-11 14:30:00
SELECT CURDATE();          -- 2025-02-11
SELECT CURRENT_TIMESTAMP;  -- 2025-02-11 14:30:00
 
-- Hive / Spark SQL
SELECT current_date();     -- 2025-02-11
SELECT current_timestamp();-- 2025-02-11 14:30:00.000
 
-- PostgreSQL
SELECT NOW();              -- 2025-02-11 14:30:00+08
SELECT CURRENT_DATE;       -- 2025-02-11

面试技巧： 写SQL的时候不要硬编码日期（比如'2025-02-11'），用current_date()让SQL自动适配。但在做数据回刷或历史分析的时候，还是要用具体日期，这时候一般通过参数传入：WHERE dt = '${bizdate}'。

二、日期加减

这是使用频率最高的操作，也是各引擎差异最大的地方。

-- MySQL
SELECT DATE_ADD('2025-01-01', INTERVAL 7 DAY);   -- 2025-01-08
SELECT DATE_SUB('2025-01-01', INTERVAL 1 MONTH);  -- 2024-12-01
 
-- Hive / Spark SQL
SELECT DATE_ADD('2025-01-01', 7);    -- 2025-01-08（直接传天数）
SELECT DATE_SUB('2025-01-01', 7);    -- 2024-12-25
SELECT ADD_MONTHS('2025-01-31', 1);  -- 2025-02-28（自动处理月末）
 
-- PostgreSQL
SELECT '2025-01-01'::DATE + INTERVAL '7 days';  -- 2025-01-08
SELECT '2025-01-01'::DATE + 7;                   -- 2025-01-08（直接加整数）

坑1：Hive的DATE_ADD只支持天，不支持月和年。 要加月得用ADD_MONTHS，要加年只能ADD_MONTHS(dt, 12)。

坑2：ADD_MONTHS的月末处理。 ADD_MONTHS('2025-01-31', 1)返回的是2025-02-28而不是2025-03-03。它会自动对齐到月末，这在计算"上月同日"的时候要注意。

我做月度报表的时候就遇到过这个问题。1月31号的月环比应该和12月31号比，但如果直接用ADD_MONTHS(dt, -1)，2月28号的上月对应的是1月28号而不是1月31号——这对不对取决于你的业务定义。

三、日期差值

-- MySQL
SELECT DATEDIFF('2025-01-10', '2025-01-01');        -- 9
SELECT TIMESTAMPDIFF(HOUR, '2025-01-01', '2025-01-02');  -- 24
 
-- Hive / Spark SQL
SELECT DATEDIFF('2025-01-10', '2025-01-01');        -- 9
-- 注意：Hive的DATEDIFF参数顺序是(结束日期, 开始日期)
 
-- PostgreSQL
SELECT '2025-01-10'::DATE - '2025-01-01'::DATE;    -- 9
SELECT EXTRACT(EPOCH FROM ('2025-01-02'::TIMESTAMP - '2025-01-01'::TIMESTAMP)) / 3600;  -- 24小时

坑：DATEDIFF的参数顺序。 MySQL是DATEDIFF(date1, date2) = date1 - date2，Hive也是DATEDIFF(end, start)。但有人习惯反着写，结果得到负数还不知道哪里错了。

四、日期格式化和解析

-- MySQL
SELECT DATE_FORMAT('2025-01-15', '%Y-%m');    -- 2025-01
SELECT STR_TO_DATE('2025/01/15', '%Y/%m/%d'); -- 2025-01-15
 
-- Hive / Spark SQL
SELECT DATE_FORMAT('2025-01-15', 'yyyy-MM');    -- 2025-01
SELECT TO_DATE('2025-01-15 14:30:00');          -- 2025-01-15
SELECT FROM_UNIXTIME(1736956200, 'yyyy-MM-dd'); -- 日期格式化
 
-- PostgreSQL
SELECT TO_CHAR('2025-01-15'::DATE, 'YYYY-MM');  -- 2025-01
SELECT TO_DATE('20250115', 'YYYYMMDD');          -- 2025-01-15

注意格式串差异：

含义	MySQL	Hive/Spark	PostgreSQL
------	-------	-----------	------------
四位年	%Y	yyyy	YYYY
两位月	%m	MM	MM
两位日	%d	dd	DD
24小时	%H	HH	HH24
分钟	%i	mm	MI
秒	%s	ss	SS

这个对照表建议收藏。我每次换引擎都要翻一遍，尤其是MySQL的分钟是%i不是%M（%M是月份英文名），坑过很多人。

五、提取日期部分

-- MySQL
SELECT YEAR('2025-01-15');    -- 2025
SELECT MONTH('2025-01-15');   -- 1
SELECT DAY('2025-01-15');     -- 15
SELECT DAYOFWEEK('2025-01-15');  -- 4（MySQL中1=周日）
SELECT WEEKDAY('2025-01-15');    -- 2（0=周一）
 
-- Hive / Spark SQL
SELECT YEAR('2025-01-15');    -- 2025
SELECT MONTH('2025-01-15');   -- 1
SELECT DAY('2025-01-15');     -- 15
SELECT DAYOFWEEK('2025-01-15');  -- 4（1=周日，和MySQL一样）
 
-- PostgreSQL
SELECT EXTRACT(YEAR FROM '2025-01-15'::DATE);       -- 2025
SELECT EXTRACT(DOW FROM '2025-01-15'::DATE);        -- 3（0=周日）
SELECT EXTRACT(ISODOW FROM '2025-01-15'::DATE);     -- 3（1=周一）

坑：星期几的编号系统不统一。 MySQL和Hive的DAYOFWEEK是1=周日，PostgreSQL的DOW是0=周日。如果你在SQL里用星期几做业务判断（比如"排除周末"），一定要搞清楚当前引擎的编号规则。

-- 过滤工作日（Hive/Spark）
WHERE DAYOFWEEK(dt) NOT IN (1, 7)  -- 1=周日, 7=周六
 
-- 过滤工作日（PostgreSQL）
WHERE EXTRACT(ISODOW FROM dt::DATE) <= 5  -- 1=周一, 5=周五

六、时间戳和日期互转

做出海业务必须掌握的技能——时区转换：

-- Unix时间戳 → 日期（Hive/Spark）
SELECT FROM_UNIXTIME(1736956200);                   -- 2025-01-15 14:30:00
SELECT FROM_UNIXTIME(1736956200, 'yyyy-MM-dd');     -- 2025-01-15
 
-- 日期 → Unix时间戳
SELECT UNIX_TIMESTAMP('2025-01-15 14:30:00');       -- 1736956200
 
-- 时区转换（MySQL）
SELECT CONVERT_TZ('2025-01-15 14:30:00', 'UTC', 'America/New_York');
-- 2025-01-15 09:30:00
 
-- 时区转换（Spark SQL）
SELECT FROM_UTC_TIMESTAMP('2025-01-15 14:30:00', 'America/New_York');
-- 2025-01-15 09:30:00
 
SELECT TO_UTC_TIMESTAMP('2025-01-15 09:30:00', 'America/New_York');
-- 2025-01-15 14:30:00

我做短剧出海业务，用户遍布全球，所有数据先统一存UTC，计算指标的时候再转成用户当地时间。这里有一个特别容易踩的坑：夏令时。美国东部时间冬天是UTC-5，夏天是UTC-4。如果你硬编码INTERVAL -5 HOUR来转时区，夏天的数据全错。一定要用时区名（America/New_York）而不是偏移量。

七、面试综合题

需求：计算2025年1月每一周的广告消耗汇总（自然周，周一到周日）。

-- Hive/Spark写法
SELECT
  DATE_FORMAT(
    DATE_SUB(dt, DAYOFWEEK(dt) - 2),  -- 本周一的日期
    'yyyy-MM-dd'
  ) AS week_start,
  DATE_FORMAT(
    DATE_ADD(DATE_SUB(dt, DAYOFWEEK(dt) - 2), 6),  -- 本周日
    'yyyy-MM-dd'
  ) AS week_end,
  SUM(cost) AS weekly_cost
FROM ad_daily_cost
WHERE dt BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY DATE_SUB(dt, DAYOFWEEK(dt) - 2)
ORDER BY week_start

这道题综合了日期加减、星期计算、格式化、分组聚合。面试的时候能一气呵成写出来，说明你对日期函数确实很熟。

另一个常见变体：按"自然月"分组求和。Hive/Spark直接用DATE_FORMAT(dt, 'yyyy-MM')就行，但注意别写成MySQL的DATE_FORMAT(dt, '%Y-%m')格式串。

刷题进度保存在当前浏览器