9. 日期与时间数据类型

时间是数据库里最容易出错的类型。同一个时间点，不同时区、不同类型存进去取出来可能不一样。这一节把时间类型彻底讲明白。

9.1 五种时间类型一览

MySQL 的时间类型有五个：

类型	格式	范围	存储	用途
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	3 字节	日期（无时间）
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 字节	时间或时间跨度
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	5 字节	日期+时间
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC	4 字节	时间戳
YEAR	YYYY	1901 ~ 2155	1 字节	年份

9.2 DATE：只存日期

DATE 只存年月日，不带时间，占 3 字节。

CREATE TABLE users (
    id INT PRIMARY KEY,
    birth_date DATE
);

INSERT INTO users VALUES(1, '1995-08-15');

格式必须是 YYYY-MM-DD，或者用 'YYYYMMDD' 这种紧凑形式也行：

INSERT INTO users VALUES(2, '19950815');

适用场景：生日、入职日期、纪念日这类”只关心哪天”的数据。

9.3 TIME：时间或时长

TIME 存时间，格式 HH:MM:SS。注意它的范围是 -838:59:59 ~ 838:59:59，能表示负数和超过 24 小时的值，所以它不限于一天内的时间，也能表示时间跨度。

CREATE TABLE tasks (
    id INT PRIMARY KEY,
    duration TIME
);

INSERT INTO tasks VALUES(1, '02:30:00');  -- 2 小时 30 分
INSERT INTO tasks VALUES(2, '-01:00:00'); -- 负 1 小时

适用场景：营业时间、任务时长、倒计时。

9.4 DATETIME：日期加时间

DATETIME 是最常用的时间类型，存完整的”日期+时间”，格式 YYYY-MM-DD HH:MM:SS，占 5 字节。

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME
);

INSERT INTO orders(created_at) VALUES('2026-07-30 14:30:00');

DATETIME 存的是字面值：你写什么就存什么，不涉及时区转换。存 '2026-07-30 14:30:00'，取出来还是 '2026-07-30 14:30:00'。

mysql> SELECT created_at FROM orders;
+---------------------+
| created_at          |
+---------------------+
| 2026-07-30 14:30:00 |
+---------------------+

适用场景：订单时间、日志时间、约定好的固定时间点。

9.5 TIMESTAMP：带时区的时间戳

TIMESTAMP 看起来和 DATETIME 一模一样，格式也是 YYYY-MM-DD HH:MM:SS，但底层完全不同：

• 存储：占 4 字节，存的是从 1970-01-01 00:00:01 UTC 起的秒数；
• 时区：写入时按当前会话时区转成 UTC 存，读取时再按会话时区转回来；
• 范围：1970-01-01 00:00:01 ~ 2038-01-19 03:14:07 UTC。

关键差异在时区转换。看个实验：

CREATE TABLE t (
    dt DATETIME,
    ts TIMESTAMP
);

-- 把会话时区设为 UTC
SET time_zone = '+00:00';

INSERT INTO t VALUES(NOW(), NOW());

mysql> SELECT * FROM t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2026-07-30 06:00:00 | 2026-07-30 06:00:00 |
+---------------------+---------------------+

-- 换成东八区再查
SET time_zone = '+08:00';

mysql> SELECT * FROM t;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2026-07-30 06:00:00 | 2026-07-30 14:00:00 |
+---------------------+---------------------+

dt（DATETIME）不变，ts（TIMESTAMP）跟着时区变了 8 小时。这就是两者最大的区别。

9.6 TIMESTAMP 的 2038 问题

TIMESTAMP 用 4 字节存秒数，最大只能存到 2038-01-19 03:14:07 UTC（北京时间 2038-01-19 11:14:07）。超过这个时间就存不下，叫”2038 问题”。

如果你的数据可能超过 2038 年（比如长期合同、出生日期），别用 TIMESTAMP，用 DATETIME。

9.7 DATETIME vs TIMESTAMP 对比

对比项	DATETIME	TIMESTAMP
存储	5 字节	4 字节
范围	1000 年 ~ 9999 年	1970 年 ~ 2038 年
时区	不转换，存字面值	按 UTC 存，按时区显示
自动更新	5.6.5 起支持	一直支持
跨时区业务	不适合	适合
远期日期	适合	不适合（2038）

选型建议：

• 创建时间、更新时间：两者都行，TIMESTAMP 更省空间；
• 用户相关的固定时间（生日、订单时间）：用 DATETIME；
• 跨国、多时区：用 TIMESTAMP；
• 超过 2038 年：必须 DATETIME。

9.8 自动初始化与自动更新

这是时间类型最实用的特性。给时间列加上默认值和自动更新，能省掉应用层很多代码。

自动初始化：插入时自动填当前时间

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

DEFAULT CURRENT_TIMESTAMP 表示：插入时不指定 created_at，就自动填当前时间。

INSERT INTO users(username) VALUES('zhangsan');

mysql> SELECT username, created_at FROM users;
+----------+---------------------+
| username | created_at          |
+----------+---------------------+
| zhangsan | 2026-07-30 14:30:00 |
+----------+---------------------+

自动更新：修改行时自动刷新时间

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ON UPDATE CURRENT_TIMESTAMP 表示：这行其他列被 UPDATE 修改时，updated_at 自动刷新成当前时间。

INSERT INTO users(username) VALUES('lisi');
-- created_at 和 updated_at 都是当前时间

UPDATE users SET username = 'lisi2' WHERE id = 1;
-- updated_at 自动变成新时间，created_at 不变

9.9 YEAR：只存年份

YEAR 只存 4 位年份，占 1 字节，范围 1901 ~ 2155。

CREATE TABLE products (
    id INT PRIMARY KEY,
    production_year YEAR
);

INSERT INTO products VALUES(1, 2026);

9.10 小数秒精度

所有时间类型都能指定小数秒精度 fsp（0~6），存到微秒级：

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_time DATETIME(3)  -- 精确到毫秒
);

INSERT INTO events(event_time) VALUES('2026-07-30 14:30:00.123');

DATETIME(3) 占 5+2=7 字节，DATETIME(6) 占 5+3=8 字节。不写 fsp 默认是 0（不带小数秒）。

9.11 常用的时间函数

时间类型经常配合函数用，这里列几个最常用的：

-- 当前日期时间
SELECT NOW(), CURRENT_TIMESTAMP, SYSDATE();

-- 当前日期
SELECT CURDATE();

-- 当前时间
SELECT CURTIME();

-- 提取部分
SELECT YEAR('2026-07-30'), MONTH('2026-07-30'), DAY('2026-07-30');

-- 日期加减
SELECT DATE_ADD('2026-07-30', INTERVAL 1 MONTH);  -- 加 1 个月
SELECT DATE_SUB('2026-07-30', INTERVAL 7 DAY);    -- 减 7 天

-- 日期差（天数）
SELECT DATEDIFF('2026-12-31', '2026-01-01');  -- 364

-- 格式化
SELECT DATE_FORMAT('2026-07-30', '%Y年%m月%d日');

9.12 时区设置

会话时区用 SET time_zone 改：

-- 设为 UTC
SET time_zone = '+00:00';

-- 设为东八区（北京时间）
SET time_zone = '+08:00';

-- 设为系统时区
SET time_zone = 'SYSTEM';

服务器全局时区在 my.cnf 里配：

[mysqld]
default-time-zone='+08:00'

查看当前时区：

SELECT @@global.time_zone, @@session.time_zone;

9.13 小结

这一节理清了时间类型：

• DATE 存日期、TIME 存时间或时长、YEAR 存年份；
• DATETIME 存字面值不带时区，范围大、适合远期；
• TIMESTAMP 按 UTC 存、按时区显示，省空间但受 2038 限制；
• DEFAULT CURRENT_TIMESTAMP 自动填创建时间；
• ON UPDATE CURRENT_TIMESTAMP 自动刷新更新时间；
• 小数秒精度 fsp 0~6，普通业务默认 0；
• 查”某一天”用范围查询别用等号，能走索引。

下一节讲 JSON、ENUM 和二进制类型，把数据类型这一篇收尾。