SQL入门：日期函数实战指南

日期时间是 SQL 中高频处理的数据类型，无论是统计 “近 7 天订单量”“本月销售额”，还是计算 “用户注册至今的天数”，都需要依赖日期函数。标准 SQL 定义了一套基础日期函数，但不同数据库（如 MySQL、PostgreSQL、SQL Server）会有扩展，本文先聚焦通用核心函数，再补充主流数据库的差异点，确保 0 基础小白也能理解和使用。

一、先明确：SQL 中的日期时间类型

在学习函数前，需先了解常见的日期时间类型（不同数据库命名略有差异，但逻辑一致）：

类型分类	标准含义	示例	常见数据库对应类型
DATE	仅日期（年 - 月 - 日）	'2024-05-20'	MySQL/SQL Server: DATE
TIME	仅时间（时：分: 秒）	'14:30:45'	MySQL/SQL Server: TIME
DATETIME/TIMESTAMP	日期 + 时间（年 - 月 - 日 时：分: 秒）	'2024-05-20 14:30:45'	MySQL: DATETIME；PostgreSQL: TIMESTAMP
YEAR	仅年份	'2024'	MySQL: YEAR；SQL Server 需用 DATE 截取

日期函数的核心作用：提取日期组件（如年、月、日）、计算日期差、生成新日期。

二、标准 SQL 核心日期函数（通用）

以下函数是 SQL 标准定义的基础功能，在主流数据库中兼容性较高，结合 “订单表（orders）” 和 “用户表（users）” 实例讲解（表结构参考：orders含create_time（下单时间），users含register_time（注册时间））。

1. 提取日期组件：获取年 / 月 / 日 / 时 / 分 / 秒

这类函数用于从日期时间中 “拆分” 出具体部分，比如 “统计每月订单量” 需要提取 “月份”，“筛选某天订单” 需要提取 “日”。

函数	作用	语法示例	结果（假设日期为 '2024-05-20 14:30:45'）
EXTRACT(单位 FROM 日期)	提取指定单位（通用度最高	EXTRACT(YEAR FROM '2024-05-20')	2024（年份）
		EXTRACT(MONTH FROM '2024-05-20')	5（月份）
		EXTRACT(DAY FROM '2024-05-20')	20（日）
		EXTRACT(HOUR FROM '2024-05-20 14:30:45')	14（小时）
YEAR(日期)	提取年份（简洁版）	YEAR('2024-05-20')	2024
MONTH(日期)	提取月份（简洁版）	MONTH('2024-05-20')	5
DAY(日期)	提取日（简洁版）	DAY('2024-05-20')	20
HOUR(时间)	提取小时	HOUR('14:30:45')	14
MINUTE(时间)	提取分钟	MINUTE('14:30:45')	30
SECOND(时间)	提取秒	SECOND('14:30:45')	45

实例 1：统计 2024 年 5 月的订单总量

SELECT COUNT(*) AS 5月订单量
FROM orders
WHERE YEAR(create_time) = 2024 
  AND MONTH(create_time) = 5;

实例 2：按 “年 - 月” 分组，统计每月订单量

SELECT 
  CONCAT(YEAR(create_time), '-', LPAD(MONTH(create_time), 2, '0')) AS 年月, -- 补0（如5→05）
  COUNT(*) AS 月订单量
FROM orders
GROUP BY 年月
ORDER BY 年月;

• 说明：LPAD(字段, 长度, 补位字符)是辅助函数，用于将月份 “5” 补成 “05”，使结果格式统一（如 “2024-05” 而非 “2024-5”）。

2. 日期计算：加减时间、求日期差

这类函数用于计算 “两个日期的间隔”（如用户注册天数）或 “在指定日期上加减时间”（如计算 3 天后的日期），是业务中最常用的场景。

（1）计算两个日期的差值：DATEDIFF

• 作用：计算两个日期之间的 “天数差”（注意：不同数据库参数顺序可能不同，标准 SQL 是 “结束日期 - 开始日期”）。
• 语法：DATEDIFF(结束日期, 开始日期)
• 结果：整数（正数表示结束日期在后面，负数表示在前面）。

实例 3：计算每个用户的 “注册至今天数”

SELECT 
  user_id,
  register_time,
  DATEDIFF(CURRENT_DATE(), register_time) AS 注册天数 -- CURRENT_DATE()获取当前日期
FROM users;

• 示例结果：若用户注册时间是 '2024-01-01'，当前日期是 '2024-05-20'，则注册天数 = 140（2024-05-20 - 2024-01-01）。

（2）在日期上加减时间：DATE_ADD/DATE_SUB

• 作用：给指定日期 “加” 或 “减” 指定单位的时间（如加 3 天、减 1 个月）。
• 语法：
  • 加时间：DATE_ADD(原日期, INTERVAL 数值 单位)
  • 减时间：DATE_SUB(原日期, INTERVAL 数值 单位)
• 支持的单位：DAY（天）、MONTH（月）、YEAR（年）、HOUR（小时）、MINUTE（分钟）等。

实例 4：计算 “订单创建后 7 天” 的日期（用于判断售后有效期）

SELECT 
  order_id,
  create_time,
  DATE_ADD(create_time, INTERVAL 7 DAY) AS 售后截止日期
FROM orders;

• 示例结果：若订单创建时间是 '2024-05-20 14:30:45'，则售后截止日期 ='2024-05-27 14:30:45'。

实例 5：计算 “3 个月前” 的日期（用于统计近 3 个月订单）

SELECT COUNT(*) AS 近3月订单量
FROM orders
WHERE create_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH);

• 逻辑：筛选 “订单创建时间 ≥ 当前日期减 3 个月” 的数据，即近 3 个月的订单。

3. 获取当前日期时间：CURRENT_DATE/CURRENT_TIMESTAMP

• • 作用：获取数据库服务器的 “当前日期” 或 “当前日期时间”，无需参数，常用于动态筛选（如 “今天的订单”）。

函数	作用	示例结果
CURRENT_DATE()	获取当前日期（仅年 - 月 - 日）	'2024-05-20'
CURRENT_TIMESTAMP()	获取当前日期时间（含时分秒）	'2024-05-20 16:45:30'
NOW()	等同于CURRENT_TIMESTAMP()（MySQL 常用）	'2024-05-20 16:45:30'

实例 6：筛选 “今天的订单”

SELECT *
FROM orders
WHERE DATE(create_time) = CURRENT_DATE(); -- DATE()截取日期部分，忽略时间

• 逻辑：DATE(create_time)将create_time（如 '2024-05-20 14:30:45'）截取为 '2024-05-20'，与当前日期对比。

4. 日期格式化：DATE_FORMAT（转换为指定字符串）

• 作用：将日期时间类型（如DATETIME）转换为 “自定义格式的字符串”（如 “2024 年 05 月 20 日”“2024-05-20 14:30”），方便展示。
• 语法：DATE_FORMAT(日期, 格式符)
• 常用格式符（不同数据库略有差异，以下是 MySQL 常用）：

格式符	含义	示例（日期 '2024-05-20 14:30:45'）
%Y	4 位年份	2024
%m	2 位月份（补 0）	05
%d	2 位日（补 0）	20
%H	24 小时制小时	14
%i	2 位分钟	30
%s	2 位秒	45

实例 7：将订单时间格式化为 “年 - 月 - 日 时：分”

SELECT 
  order_id,
  DATE_FORMAT(create_time, '%Y-%m-%d %H:%i') AS 格式化下单时间
FROM orders;

• 示例结果：'2024-05-20 14:30'（去掉了秒，格式更简洁）。

三、主流数据库日期函数差异（避坑重点）

标准 SQL 的核心函数在不同数据库中基本通用，但部分函数的名称或参数顺序有差异，新手容易踩坑，这里整理高频差异点：

功能需求	MySQL 语法	PostgreSQL 语法	SQL Server 语法
计算日期差（天数）	DATEDIFF(结束日期, 开始日期)	(结束日期 - 开始日期)::INTEGER	DATEDIFF(day, 开始日期, 结束日期)
日期加时间	DATE_ADD(日期, INTERVAL 3 DAY)	日期 + INTERVAL '3 days'	DATEADD(day, 3, 日期)
提取月份	MONTH(日期)	EXTRACT(MONTH FROM 日期)	MONTH(日期)
当前日期时间	NOW() / CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP	GETDATE()
日期格式化	DATE_FORMAT(日期, '%Y-%m-%d')	TO_CHAR(日期, 'YYYY-MM-DD')	CONVERT(VARCHAR, 日期, 23)

避坑示例：计算日期差的差异

• MySQL：DATEDIFF('2024-05-20', '2024-05-10') → 结果 10（结束 - 开始）；
• SQL Server：DATEDIFF(day, '2024-05-10', '2024-05-20') → 结果 10（参数顺序是 “单位，开始，结束”）；
• PostgreSQL：('2024-05-20' - '2024-05-10')::INTEGER → 结果 10（直接减，转换为整数）。

四、高频业务场景实战（综合运用）

掌握单个函数后，需结合业务场景综合使用，以下是 3 个典型场景：

场景 1：统计 “近 7 天每天的订单量”（含无订单日期补 0）

需求：展示近 7 天（含今天）每天的订单数，即使某天没有订单，也要显示 0（避免结果缺失日期）。

-- 步骤1：生成近7天的日期列表（临时表）
WITH date_list AS (
  SELECT CURRENT_DATE() - INTERVAL 6 DAY AS dt UNION ALL
  SELECT CURRENT_DATE() - INTERVAL 5 DAY UNION ALL
  SELECT CURRENT_DATE() - INTERVAL 4 DAY UNION ALL
  SELECT CURRENT_DATE() - INTERVAL 3 DAY UNION ALL
  SELECT CURRENT_DATE() - INTERVAL 2 DAY UNION ALL
  SELECT CURRENT_DATE() - INTERVAL 1 DAY UNION ALL
  SELECT CURRENT_DATE()
),
-- 步骤2：统计每天实际订单量
daily_orders AS (
  SELECT 
    DATE(create_time) AS order_date,
    COUNT(*) AS order_count
  FROM orders
  WHERE create_time >= CURRENT_DATE() - INTERVAL 6 DAY -- 近7天
  GROUP BY order_date
)
-- 步骤3：关联日期列表和实际订单量，无订单补0
SELECT 
  dl.dt AS 日期,
  COALESCE(do.order_count, 0) AS 订单量 -- COALESCE：null值替换为0
FROM date_list dl
LEFT JOIN daily_orders do ON dl.dt = do.order_date
ORDER BY dl.dt;

• 核心逻辑：先手动生成近 7 天的完整日期（避免缺失），再用LEFT JOIN关联实际订单数据，最后用COALESCE将无订单的日期补 0。

场景 2：计算用户的 “首次下单时间” 与 “注册时间的间隔”（判断转化速度）

需求：对有下单记录的用户，计算 “注册到首次下单” 的天数，分析转化效率。

SELECT 
  u.user_id,
  u.register_time,
  MIN(o.create_time) AS 首次下单时间, -- MIN()获取最早的下单时间
  DATEDIFF(MIN(o.create_time), u.register_time) AS 注册到下单天数
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id -- 只保留有下单的用户
GROUP BY u.user_id, u.register_time
HAVING 注册到下单天数 >= 0; -- 排除异常数据（下单时间早于注册时间）

• 核心逻辑：用INNER JOIN关联用户和订单表，MIN(o.create_time)获取每个用户的首次下单时间，再用DATEDIFF计算间隔。

场景 3：筛选 “每月最后一天” 的订单（用于月末对账）

需求：提取每个月最后一天的所有订单，用于财务对账。

SELECT *
FROM orders
WHERE DATE(create_time) = LAST_DAY(create_time); -- LAST_DAY()获取当月最后一天

• 说明：LAST_DAY(日期)是 MySQL 的扩展函数，直接返回该日期所在月份的最后一天（如LAST_DAY('2024-05-20')→'2024-05-31'）；PostgreSQL 需用(DATE_TRUNC('month', 日期) + INTERVAL '1 month - 1 day')::DATE实现类似功能。

五、新手常见误区与避坑指南

1. 误区 1：直接用字符串比较日期错！如create_time >= '2024-5-20'，虽然部分数据库能识别，但格式不规范（月份未补 0），可能导致计算错误。正确做法：用日期函数生成标准日期，如create_time >= DATE('2024-05-20')。

2. 误区 2：忽略日期时间的 “时间部分”错！如筛选 “2024-05-20” 的订单，用create_time = '2024-05-20'会只匹配 “2024-05-20 00:00:00” 的订单，漏掉当天其他时间的订单。正确做法：截取日期部分，DATE(create_time) = '2024-05-20'。

3. 误区 3：不同数据库函数混用错！如在 PostgreSQL 中用DATE_FORMAT（这是 MySQL 函数），会报错。正确做法：先确认所用数据库的函数语法（参考本文第三部分差异表）。

4. 误区 4：处理 NULL 值时未用 COALESCE错！如计算注册天数时，若register_time为 NULL，DATEDIFF结果会是 NULL，导致统计缺失。正确做法：用COALESCE(register_time, CURRENT_DATE())将 NULL 替换为默认日期（如当前日期）。