MariaDB 教程 / 计算日期间隔、时间差并设置显示格式
DATE_ADD 用于增加时间间隔,DATEDIFF 与 TIMESTAMPDIFF 用于计算不同含义的差值,DATE_FORMAT 则生成显示文本。计算前先区分日历日期差与完整时间单位;查询整个期间时,使用“包含起点、不包含下一期间起点”的范围,可以明确处理边界。
先确定单位与业务含义
“一个月后到期”“跨过了几天的日期”“经过了几个完整小时”并不是同一个问题。先确定问题,再选择函数。计算应基于日期时间类型,而不是把值变成带地区格式的字符串,再尝试转回日期。
| 函数 | 用途 | 参数规则 |
|---|---|---|
| DATE_ADD | 增加指定间隔 | 例如 INTERVAL 1 MONTH |
| DATEDIFF | 计算日期部分的天数差 | 第一个日期减第二个日期 |
| TIMESTAMPDIFF | 计算指定单位的整数差 | 先给单位,再给起点和终点;终点减起点 |
| DATE_FORMAT | 生成显示字符串 | 例如 %Y-%m 表示年份与月份 |
存储类型和时区转换可先对照 DATE、DATETIME 与 TIMESTAMP 教程。函数规则见官方 DATE_ADD、DATEDIFF、TIMESTAMPDIFF与 DATE_FORMAT 文档。
在同一连接中比较固定日期
以学习账号连接 MariaDB 客户端,先执行 USE shopcourse;,再运行 SELECT VERSION(), DATABASE(), @@SESSION.time_zone; 核对环境。下面使用固定日期,因此预期结果不随运行当天变化。日期字面量统一采用明确的年—月—日顺序,避免把月与日混淆。
这些 SELECT 可直接在客户端执行;需要保存为文件时,可参照 SQL 文件与 SOURCE 使用方法。注意先选择练习数据库,后面的建表实验也需要在 shopcourse 中执行。
SELECT DATE_ADD('2026-01-31',INTERVAL 1 MONTH);
SELECT DATE_ADD('2026-01-31',INTERVAL 30 DAY);
从 2026 年 1 月 31 日增加一个月,得到 2026-02-28;增加 30 天则得到 2026-03-02。一个日历月并不固定等于 30 天。当目标月份没有原来的日期编号时,月份计算会调整到该月有效的最后一天。
可以只把年份 2026 改为 2024 再比较:增加一个月会落在闰年的 2 月 29 日。核对后恢复原年份。订阅或合约的到期规则还应说明“月末顺延”究竟是什么意思,不能只看到函数算出了合法日期,就认定符合业务政策。
区分参数顺序与被忽略的时间部分
SELECT DATEDIFF('2026-02-02 01:00:00','2026-02-01 23:00:00'),
TIMESTAMPDIFF(HOUR,'2026-02-01 23:00:00','2026-02-02 01:00:00');
这条查询得到 1 和 2。前者表示两个日期部分相差一天:DATEDIFF 忽略时分秒,用第一个日期减第二个日期。后者表示从 2 月 1 日 23:00 到 2 月 2 日 01:00,有两个完整小时;TIMESTAMPDIFF 的参数则是“单位、起点、终点”。不要把两个函数的日期参数顺序机械照抄。
只交换各自的起止值后,结果会变为 -1 与 -2。核对符号后恢复原来的顺序。若把小时差的终点改为 01:59:59,整数小时结果仍为 2,剩余的分钟与秒没有作为小数部分返回。需要更细的差值时,应选择相应单位,并明确怎样换算与舍入。
涉及地区时区或夏令时的实际经过时间时,还要确定保存的是当地钟面时间,还是统一时基上的时刻。两个 DATETIME 值本身不会自动携带完整的地区转换规则。本例只说明固定字段值上的日期与单位计算,不能代替跨时区的时长政策。
用 DATE_FORMAT 显示日期,不改写存储值
SELECT DATE_FORMAT('2026-02-05 12:34:56','%Y-%m'),
DATE_FORMAT('2026-02-05 12:34:56','%Y-%m-%d %H:%i:%s');
输出依次为字符串 2026-02 和 2026-02-05 12:34:56。其中 %Y 是四位年份,%m 是月份,%d 是日,%H 是小时,%i 才是分钟,%s 是秒。把分钟误写成 %m,会显示月份而不是分钟,应对照一个月份与分钟不同的输入检查。
格式化只生成展示结果,不会把原来保存的 DATETIME 改成另一种日期。需要筛选、排序或继续运算时,应保留合适的原始类型。包含月份名称或星期名称的格式可能受会话语言设置影响,导出供其他程序读取时,应使用双方明确约定的格式。
用排除上界的范围覆盖整个月
先通过 SHOW TABLES; 确认 dates_21_lab 不存在。若已有同名表,先检查结构与数据,确认用途;不要覆盖现有对象。下面是独立的完整建表、插入与查询,不依赖前面教程的商品表,执行前应确认当前数据库为 shopcourse。
CREATE TABLE dates_21_lab (event_id INT PRIMARY KEY, occurred_at DATETIME NOT NULL)
ENGINE=InnoDB;
INSERT INTO dates_21_lab VALUES
(1,'2026-02-01 00:00:00'),(2,'2026-02-28 23:59:59'),(3,'2026-03-01 00:00:00');
SELECT event_id FROM dates_21_lab
WHERE occurred_at >= '2026-02-01' AND occurred_at < '2026-03-01'
ORDER BY event_id;
结果应为编号 1、2,编号 3 恰好位于 3 月 1 日零点,因此不属于二月份。条件左侧的 >= 包含 2 月 1 日的起点,右侧的 < 排除下一月的起点;它不是“两个端点都包含”的范围。
使用下个月的起点,比把上限硬写成 2026-02-28 23:59:59 更能适应精度变化。例如另一个 DATETIME(6) 字段若保存 2026-02-28 23:59:59.999999,它仍然早于 3 月 1 日,但会被“最多到 23:59:59”的条件漏掉。不要假定所有时间字段都只有整秒精度。
直接对日期列使用上下界,也让查询的目标范围更加清楚。是否能有效使用索引、实际读取多少行,需要结合索引和执行计划判断;本例返回两行,不是对性能的测量。修改时间范围时,始终同时检查起点、终点和恰好位于边界的记录。
检查无效输入并重复小型实验
结果出现 NULL 或警告时,先检查输入格式、月份天数、闰年与 SQL 模式。转换碰巧接受了不完整日期或零日期,并不代表它就是有效业务日期。缺失输入究竟应拒绝、保留为 NULL,还是使用某个默认值,应先规定清楚,不要直接替换成当前日期来掩盖问题。
可再次读取三条原始记录,确认格式化和筛选没有改写发生时间,再重复范围查询,应仍返回 1、2。若结果变化,先查看实际数据、当前数据库与查询条件,而不是立即重建表。
结束后可以保留练习表。若已确认记录无需保存,先检查表定义并备份所需内容,再执行下面的定向清理。DROP TABLE 会删除表定义和全部行,普通 ROLLBACK 不能恢复;不要把它混入需要回滚的数据修改实验。
DROP TABLE shopcourse.dates_21_lab;




