MariaDB 教程 / 使用 DATE、DATETIME 和 TIMESTAMP 保存日期与时间

DATE 保存日期,DATETIME 保存年月日时分秒字段,TIMESTAMP 则在输入、输出时按连接时区与 UTC 转换。选择前先判断需要的是本地日历时间,还是事件发生的同一个瞬间;相同 TIMESTAMP 在两个会话时区下可能显示不同钟点。不要仅因显示格式相似,就把这三种类型当成等价。

区分日期、本地钟点和事件瞬间

类型 用途示例 时区行为
DATE 生日或预约日期 本身不保存钟点或时区标识
DATETIME 2026-10-13 09:00:00 这样的日历字段 仅改变会话时区,不会自动转换保存的年月日时分秒
TIMESTAMP 日志事件或接收事件的时刻 输入、输出时在会话时区与 UTC 之间转换

协调世界时(Coordinated Universal Time,UTC)是比较事件瞬间的共同参照。以本例的 +09:00 偏移为例,当地 09:00 与同日 UTC 00:00 可以表示同一个瞬间。+09:00 是这里主动选择的比较条件,不能从文章语言、浏览器地区或电脑位置推断当前数据库连接也在这个偏移下。在字符串后写上 UTC,也不会自动配置连接时区。

DATETIME 本地钟点本身不保存地区时区及夏令时规则。应用可能根据用途统一转换为 UTC,也可能保存本地字段和独立的地区时区标识。TIMESTAMP 同样不保留最初输入时的时区名称。本篇按普通日期类型语义演示,正常文件会明确设置 STRICT_TRANS_TABLES;ORACLE 兼容模式中 DATE 带时间部分的语义存在差异,应按对应模式文档判断。

检查服务器版本和连接时区

按照命令行连接与 SOURCE 执行方法,用 course_user 进入练习服务器,确认 shopcourse 可用。下列检查在客户端连接内部执行:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

selected_database 应为 shopcourse。正常示例可保存为完整 UTF-8 SQL 文件,以实际本机路径执行。故意报错的语句单独运行,避免中途错误停止文件剩余内容。再查看三个不同层级的时区信息:

SELECT @@SESSION.time_zone AS session_zone,
       @@GLOBAL.time_zone AS global_zone,
       @@system_time_zone AS system_zone;

SESSION 是当前连接设置;GLOBAL 为新连接提供默认值;SYSTEM 根据服务器操作系统的时区解释。不能只根据机器位于哪里就认定会话的实际设置。本练习明确使用 +09:00 与 +00:00 固定偏移,不依赖命名时区表,也不修改操作系统时区。

DATE 和 DATETIME 的正常年份范围为 1000 至 9999,TIMESTAMP 上限则与版本有关:MariaDB 11.5 之前通常截至 2038-01-19 03:14:07 UTC,从 11.5 起截至 2106-02-07 06:28:15 UTC。正常下限为 1970-01-01 00:00:01 UTC,特殊零值属于另一个问题。先用 SELECT VERSION() 确认实际服务器,再核对 TIMESTAMP 的版本范围说明,不要将某个发行版包的行为套用到所有版本。

插入一次,只改变会话时区

本例新建独立的 time_lab 表,将相同的年月日时分秒分别输入 DATETIME 和 TIMESTAMP,然后只改变会话时区来比较结果。先确认该表名未被重要对象占用。示例保存原 SQL 模式与时区,明确声明 TIMESTAMP NULL DEFAULT NULL,避免依赖随版本、配置变化的第一个 TIMESTAMP 隐式默认行为。表结构定义与检查教程说明了为何需要核对实际保存的定义。

USE shopcourse;
SET @saved_sql_mode = @@SESSION.sql_mode;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET @saved_time_zone = @@SESSION.time_zone;
SET SESSION time_zone = '+09:00';
CREATE TABLE time_lab (
  item_id INT PRIMARY KEY,
  event_date DATE NOT NULL,
  wall_time DATETIME NOT NULL,
  instant_time TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO time_lab VALUES
  (1, '2026-10-13', '2026-10-13 09:00:00', '2026-10-13 09:00:00');
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+00:00';
SELECT item_id, event_date, wall_time, instant_time FROM time_lab;
SET SESSION time_zone = '+09:00';
SELECT item_id, wall_time, instant_time FROM time_lab;
查询偏移 event_date wall_time instant_time
插入后的 +09:00 2026-10-13 2026-10-13 09:00:00 2026-10-13 09:00:00
+00:00 2026-10-13 2026-10-13 09:00:00 2026-10-13 00:00:00
恢复 +09:00 2026-10-13 2026-10-13 09:00:00 2026-10-13 09:00:00

最后一次 SELECT 只列出编号与两个时间列;上表的 event_date 仍是同一行保存的日期,用于对照。TIMESTAMP 并没有变成两个不同事件,而是同一个瞬间按不同连接偏移显示。DATETIME 保持其日历字段,DATE 也没有因为本例切换时区而变化。整个比较没有对这条记录执行 UPDATE。

根据应用需要的行为选择类型,可分别查看 DATETIME 定义及 TIMESTAMP 转换规则。如果两个连接同时查询,显示差异也应先结合各自 SESSION.time_zone 解释,不能仅凭钟点不同就认定数据被改写。

明确指定自动值与小数秒精度

希望插入时自动使用当前创建时刻,应明确声明 DEFAULT CURRENT_TIMESTAMP。希望更新时自动变更,则另行决定 ON UPDATE CURRENT_TIMESTAMP。创建时间、最后修改时间与外部事件实际发生时间是不同含义,一个自动维护的修改时间列不会自然变成事件发生时间。

DATETIME(6) 和 TIMESTAMP(6) 指定最多六位小数秒,6 不是年份范围,也不是整个时间字符串长度。列与默认表达式的小数秒精度应有意匹配,不必为每条记录都盲目要求微秒。

输入优先使用 YYYY-MM-DD 与 YYYY-MM-DD HH:MM:SS 这类不易歧义的格式。10/11/26 等依赖地区习惯的字符串,以及把日期保存为任意文本的做法,会增加解释与比较的困难。DATE 官方参考说明了日期格式、范围及特殊值条件。

排查非法日期和意外时间差

正常文件运行后,在同一严格模式连接中单独执行下面的预期失败语句。2026 年 2 月没有 30 日:

INSERT INTO time_lab VALUES (2, '2026-02-30', '2026-10-13 09:00:00', NULL);

应出现 Incorrect date value 错误,执行 SELECT COUNT(*) FROM time_lab 后仍应只有原来的一行。SQL 模式会影响非法日期和零值的处理,不能为了绕过错误就随意改变模式,否则可能改变数据含义。条件详见 SQL_MODE 官方说明。

查询结果意外相差九小时,先检查列类型和 @@SESSION.time_zone。准备执行加减九小时的 UPDATE 之前,应确认应用和连接是否已经各转换了一次;显示转换问题不能直接当成存储值错误来修补。

固定偏移不能表达整个地区的夏令时历史。使用命名时区需要合适的服务器时区数据及应用规则。本练习只验证固定偏移转换,不能据此认定已经覆盖地区时区或夏令时场景。

恢复原连接设置

SET SESSION time_zone = @saved_time_zone;
SET SESSION sql_mode = @saved_sql_mode;
SELECT @@SESSION.time_zone, @@SESSION.sql_mode;

恢复命令必须在保存这些用户变量的同一连接中执行。断开后变量不会传入新连接,应检查新会话,而不是使用已经为空的保存值。完成后核对返回的会话时区和 SQL 模式,确认恢复结果。

重做前执行 SELECT DATABASE() 和 SHOW CREATE TABLE time_lab,确认该对象确实是本篇可丢弃的练习表。要保留的数据先备份,确认后仅删除这个表:

DROP TABLE shopcourse.time_lab;

DROP TABLE 同时移除表结构和数据,普通 ROLLBACK 不能恢复。随后可以重新运行完整正常示例。首次商品练习中的 products 表不在此次重置范围内,也不需要更改服务器的全局时区或其他连接。