如何从SQL日期获取天数_利用DAY函数快速索引-SQL-PHP中文网

DAY()函数仅返回日期的“日”部分（1–31），不表示天数差或年中第几天；常见误用包括错误排序、漏跨月逻辑；需用DATEDIFF()等计算天数差，并通过生成列+索引优化查询性能。

如何从sql日期获取天数_利用day函数快速索引

DAY() 函数返回的是日期中的「日」，不是从某天起的天数

很多人看到 DAY() 就以为能算“距离1970-01-01有多少天”或“今年第几天”，其实它只取日期字段的 日部分（即 1–31 的整数）。比如 DAY('2024-03-15') 返回 15，和“第几天”“经过天数”完全无关。

常见错误现象：
– 在 ORDER BY 中用 DAY(created_at) 想按“创建日期先后”排序，结果变成按每月几号排（1号全挤一起）
– 用 WHERE DAY(date_col) = 1 查“每月第一天”，却漏掉跨月逻辑（比如想查“最近30天里所有1号”，但没加时间范围约束）

真正要算“从某起点到该日期的总天数”，该用 DATEDIFF()（MySQL/SQL Server）或 EXTRACT(EPOCH FROM ...) + 运算（PostgreSQL）
DAY() 唯一适合的场景：筛选或分组“每月几号”，比如发工资日固定在每月 15 号，查所有发薪记录：WHERE DAY(pay_date) = 15
注意时区影响：如果列是 TIMESTAMP WITH TIME ZONE（如 PostgreSQL），DAY() 结果依赖会话时区，可能同一条记录在不同时区会返回不同值

MySQL 里想快速索引“每月几号”，DAY() 本身不能走索引

DAY(date_col) 是函数调用，直接写在 WHERE 或 ORDER BY 里会让 MySQL 无法使用 date_col 上的普通 B-tree 索引——哪怕你给 date_col 加了索引，执行计划里也会显示 type: ALL（全表扫描）。

验证方式：用 EXPLAIN SELECT * FROM orders WHERE DAY(created_at) = 10;，看 key 列是否为 NULL

替代方案：加一个生成列 + 索引（MySQL 5.7+）：

ALTER TABLE orders ADD COLUMN day_of_month TINYINT UNSIGNED AS (DAY(created_at)) STORED;<br>CREATE INDEX idx_day_of_month ON orders(day_of_month);

或者业务层改写查询：不要用 DAY()，改用范围查询，比如“查每月10号的数据”，实际等价于 WHERE created_at >= '2024-01-10' AND created_at = '2024-02-10' ... ——虽然麻烦，但能走索引

PostgreSQL 和 SQL Server 的 DAY() 行为一致，但索引优化路径不同

三者语法相同：DAY(date_col) 都返回 1–31 整数，语义无差别。但底层优化机制差异大，容易踩坑。

PostgreSQL：不支持函数索引直接加速 DAY(col)，但可以用表达式索引：CREATE INDEX idx_day_expr ON tbl ((DAY(created_at))); —— 注意括号必须完整，且查询时必须写成 WHERE DAY(created_at) = 10 才命中
SQL Server：支持计算列索引，但需先 ADD COLUMN day_part AS DAY(created_at) PERSISTED，再对新列建索引；PERSISTED 关键字不能少，否则索引无效
统一陷阱：所有数据库中，DAY(NULL) 返回 NULL，不是 0 或报错。如果列允许 NULL，WHERE DAY(col) = 10 会自动过滤掉 NULL 行，这点常被忽略

真要“快速索引天数差”，别碰 DAY()，改用 DATEDIFF() + 计算列

如果目标是按“距今天多少天”“距上线日多少天”来查询或排序，DAY() 完全无用。必须用日期差函数，并配合存储计算列才能高效索引。

MySQL 示例：

ALTER TABLE events ADD COLUMN days_since_launch INT AS (DATEDIFF(event_date, '2023-06-01')) STORED;<br>CREATE INDEX idx_days_since_launch ON events(days_since_launch);