一、基础语法结构

（表格可左右滚动）

简单理解：

YEAR() = “看看这是哪一年”

MONTH() = “目前是几月份？”

DAY() = “今天是这个月的第几天？”

EXTRACT() = “瑞士军刀式时间提取器”

二、语法元素解析

1. 核心参数拆解

2. 返回值特性对比

3. 特殊日期处理

-- 闰年测试 (MySQL)
SELECT DAY('2024-02-29');  -- 返回29

-- 月末边界测试 (SQL Server)
SELECT DAY('2023-04-31');  -- 错误: 无效日期

-- 跨年测试 (PostgreSQL)
SELECT EXTRACT(YEAR FROM '2023-12-31 23:59:59'::timestamp) 
-- 返回2023 (不因时间跨年改变)

三、创建模拟数据

1. 创建电商订单表

CREATE TABLE orders (
    order_id VARCHAR(10) PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE,              -- 订单日期
    delivery_time TIMESTAMP,       -- 配送时间戳
    amount DECIMAL(10,2)           -- 订单金额
);

2. 插入跨日期数据

INSERT INTO orders VALUES 
    ('O1001', 101, '2023-01-15', '2023-01-18 14:30:00', 150.00),
    ('O1002', 102, '2023-02-28', '2023-03-02 09:15:00', 299.00),
    ('O1003', 103, '2023-04-31', NULL, 450.00),  -- 无效日期测试
    ('O1004', 101, '2024-02-29', '2024-03-01 10:00:00', 175.50),  -- 闰年测试
    ('O1005', 104, '2023-12-31', '2024-01-02 16:45:00', 899.00),  -- 跨年测试
    ('O1006', 105, NULL, '2023-11-30 12:00:00', 120.00);  -- 空日期测试

3. 数据预览：

四、函数应用示例

1、基础日期提取

-- 提取订单年份和月份 (MySQL)
SELECT 
    order_id,
    order_date,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    DAY(order_date) AS order_day
FROM orders;

**输出结果(节选)**：

2、时间维度聚合分析

-- 按月统计销售业绩 (SQL Server)
SELECT 
    YEAR(order_date) AS sales_year,
    MONTH(order_date) AS sales_month,
    COUNT(order_id) AS order_count,
    SUM(amount) AS total_amount
FROM orders
WHERE order_date IS NOT NULL
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY sales_year DESC, sales_month;

输出结果：

3、混合类型日期处理

-- 从时间戳提取配送日期 (PostgreSQL)
SELECT 
    order_id,
    delivery_time,
    EXTRACT(YEAR FROM delivery_time) AS deliver_year,
    EXTRACT(MONTH FROM delivery_time) AS deliver_month,
    EXTRACT(DAY FROM delivery_time) AS deliver_day
FROM orders
WHERE delivery_time IS NOT NULL;

**输出结果(节选)**：

4、日期有效性校验

-- 检测订单日期合法性 (MySQL)
SELECT 
    order_id,
    order_date,
    CASE 
        WHEN DAY(order_date) IS NULL THEN '无效日期'
        WHEN DAY(order_date) > DAY(LAST_DAY(order_date)) THEN '日期越界'
        ELSE '有效日期'
    END AS date_validity
FROM orders;

输出结果：

五、跨平台解决方案

1. 标准ANSI SQL方案

-- 通用提取方法
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    EXTRACT(DAY FROM order_date) AS day
FROM orders;

2. SQLite特殊方案

-- SQLite使用strftime函数
SELECT 
    strftime('%Y', order_date) AS year,   -- 返回字符串
    CAST(strftime('%m', order_date) AS INTEGER) AS month,
    CAST(strftime('%d', order_date) AS INTEGER) AS day
FROM orders;

3. 空值安全处理

-- 处理NULL值的跨平台方案
SELECT 
    COALESCE(EXTRACT(YEAR FROM order_date), 0) AS year,
    COALESCE(EXTRACT(MONTH FROM order_date), 0) AS month,
    COALESCE(EXTRACT(DAY FROM order_date), 0) AS day
FROM orders;

六、企业级应用场景

1. 用户行为分析

-- 用户月活跃天数统计
SELECT
    user_id,
    COUNT(DISTINCT DAY(activity_date)) AS active_days,
    COUNT(DISTINCT CONCAT(YEAR(activity_date), MONTH(activity_date))) AS active_months
FROM user_activity
GROUP BY user_id;

2. 财务周期计算

-- 判断季度归属
SELECT 
    order_date,
    CASE 
        WHEN MONTH(order_date) BETWEEN 1 AND 3 THEN 'Q1'
        WHEN MONTH(order_date) BETWEEN 4 AND 6 THEN 'Q2'
        WHEN MONTH(order_date) BETWEEN 7 AND 9 THEN 'Q3'
        ELSE 'Q4'
    END AS fiscal_quarter
FROM orders;

3. 自动报表生成

-- 动态生成月度报告表名
SET @report_month = MONTH(CURRENT_DATE());
SET @report_year = YEAR(CURRENT_DATE());

SET @table_name = CONCAT('sales_report_', @report_year, '_', LPAD(@report_month, 2, '0'));

-- 生成动态SQL
SET @sql = CONCAT('CREATE TABLE ', @table_name, ' (...)');
PREPARE stmt FROM @sql;
EXECUTE stmt;

4. 生日营销活动

-- 查找当月生日客户
SELECT 
    customer_id,
    name,
    birth_date
FROM customers
WHERE MONTH(birth_date) = MONTH(CURRENT_DATE());

七、性能优化方案

1. 函数索引优化

-- MySQL函数索引
ALTER TABLE orders ADD INDEX idx_order_year ((YEAR(order_date)));
ALTER TABLE orders ADD INDEX idx_order_month ((MONTH(order_date)));

-- PostgreSQL表达式索引
CREATE INDEX idx_order_year ON orders (EXTRACT(YEAR FROM order_date));

2. 持久化计算列

-- SQL Server持久化列
ALTER TABLE orders
ADD order_year AS YEAR(order_date) PERSISTED;
CREATE INDEX idx_order_year ON orders(order_year);

3. 预防性优化技巧

-- 错误：索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023;

-- 正确：范围扫描
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

八、函数选择决策树

graph TD
    A[需要提取日期部分] --> B{数据库类型}
    B -->|MySQL/SQL Server| C[YEAR/MONTH/DAY]
    B -->|PostgreSQL/BigQuery| D[EXTRACT]
    B -->|SQLite| E[strftime]
    A --> F{需要空值处理}
    F -->|是| G[COALESCE + 默认值]
    F -->|否| H[直接调用]
    A --> I{性能关键路径}
    I -->|是| J[预计算列 + 索引]
    I -->|否| K[直接函数调用]

九、行业实践指南

1. 电商系统：
2. 使用MONTH()生成月度销售看板
3. 结合DAY()分析日销售波动
4. 金融系统：
5. YEAR()生成年报数据分区
6. 使用EXTRACT(QUARTER)计算季度报表
7. 社交应用：
8. 通过MONTH(birth_date)推送生日祝福
9. DAY(register_date)计算用户活跃日
10. 物联网系统：
11. 使用EXTRACT(HOUR FROM timestamp)分析设备时段负载
12. 结合YEAR()+MONTH()进行时间序列存储分区

提议：

在WHERE子句中避免直接使用日期函数

频繁查询的日期部分提议使用持久化列

关键业务系统采用EXTRACT保证跨平台兼容

用户界面展示优先使用YEAR()/MONTH()/DAY()简化逻辑