日期时间类型

导学

日期时间是业务系统的"血脉"——订单时间、登录时间、有效期、生日……MySQL 5.7 提供多种日期时间类型，选错会导致时区混乱、范围溢出或精度不足。

定义

日期时间类型：用于存储日期、时间或日期时间组合的数据类型。MySQL 5.7 核心类型包括 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR。

类型对比

DATETIME vs TIMESTAMP：最关键的选择

SQL 示例

场景一：DATETIME — 记录不变的时间点

常量演示：

SELECT
    '大翔' AS emp_name,
    '2026-01-10 09:00:00' AS event_time,
    YEAR('2026-01-10 09:00:00') AS year_val,
    DATE_FORMAT('2026-01-10 09:00:00', '%Y-%m-%d %H:%i') AS formatted
UNION ALL
SELECT
    '白歌',
    '2025-09-01 08:30:00',
    YEAR('2025-09-01 08:30:00'),
    DATE_FORMAT('2025-09-01 08:30:00', '%Y-%m-%d %H:%i');

结果：

结果解读：DATETIME 存储原始日期时间，不涉及时区转换。存储的是什么，查询出来就是什么。

适用：生日、创建时间（如果业务约定统一时区）、历史日期。

场景二：TIMESTAMP — 自动时区转换

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '登录时间，自动转客户端时区'
);

INSERT INTO employees (emp_name) VALUES ('大翔');

当前表数据（假设会话时区为 +08:00）：

验证时区转换：

-- 查看当前会话时区
SELECT @@session.time_zone;

-- 将时区改为 UTC，再次查询
SET SESSION time_zone = '+00:00';
SELECT * FROM employees;

修改时区后的数据：

结果解读：

• 原始会话时区 +08:00 时，login_time 显示为 2026-06-10 15:30:00
• 将时区改为 UTC（+00:00）后，同一行数据显示为 2026-06-10 07:30:00
• 这说明 TIMESTAMP 在存储时转换为 UTC，查询时按会话时区转换回本地时间
• DATETIME 不会有这种变化，存储和查询的值始终相同

特性：

• 存储时转换为 UTC，查询时按会话时区转换回本地时间
• 支持 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP
• 范围受限于 2038 年（4 字节 Unix 时间戳的固有问题）

场景三：自动更新时间戳

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

INSERT INTO employees (emp_name) VALUES ('大翔');

插入后表数据：

更新操作：

UPDATE employees SET emp_name = '白歌' WHERE emp_id = 1;

SELECT * FROM employees;

更新后表数据：

结果解读：

• 插入时，created_at 和 updated_at 都自动设为当前时间
• 更新行时，updated_at 自动刷新为当前时间，created_at 保持不变
• 这是追踪记录变更时间的最佳实践

DATE、TIME、YEAR 类型演示

常量演示：

SELECT
    '大翔' AS emp_name,
    '1990-05-20' AS birth_date,
    '09:00:00' AS work_start,
    2012 AS grad_year,
    DATEDIFF('2026-06-10', '1990-05-20') AS days_alive,
    ADDTIME('09:00:00', '08:00:00') AS work_end
UNION ALL
SELECT
    '白歌',
    '1985-12-15',
    '08:30:00',
    2008,
    DATEDIFF('2026-06-10', '1985-12-15'),
    ADDTIME('08:30:00', '08:00:00');

结果：

结果解读：

• DATE 只存储日期部分，适合生日、纪念日等不需要时间的场景
• TIME 只存储时间部分，适合班次、营业时长等场景
• YEAR 只存储年份，仅占 1 字节，适合仅需年份的场景（如毕业年份）
• DATEDIFF('2026-06-10', birth_date) 计算从出生到参考日期的总天数
• ADDTIME(work_start, '08:00:00') 在上班时间上加 8 小时得到下班时间

TIMESTAMP 2038 问题演示

CREATE TABLE future_plans (
    plan_id INT PRIMARY KEY,
    expire_time TIMESTAMP COMMENT '保险到期时间'
);

-- 以下插入在严格模式下会失败
INSERT INTO future_plans VALUES (1, '2040-01-01 00:00:00');

结果解读：TIMESTAMP 使用 4 字节有符号整数存储从 1970-01-01 开始的秒数，最大值为 2147483647，对应 2038-01-19 03:14:07 UTC。2040-01-01 超出此范围，插入会失败或产生警告。存储未来时间（如保险到期日、合同有效期）必须使用 DATETIME。

常见误区

面试考点

Q：DATETIME 和 TIMESTAMP 的区别？

1. 范围：DATETIME 1000-9999 年，TIMESTAMP 1970-2038 年；2. 时区：TIMESTAMP 存储 UTC 并自动按会话时区转换，DATETIME 不转换；3. 空间：TIMESTAMP 4 字节，DATETIME 5 字节；4. 自动更新：TIMESTAMP 支持 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE。

Q：2038 年问题是什么？

TIMESTAMP 使用 4 字节有符号整数存储从 1970-01-01 开始的秒数，最大值为 2147483647，对应 2038-01-19 03:14:07 UTC。超过这个时间会溢出。DATETIME 没有此问题。

Q：如何查询某个月份的数据？

推荐 WHERE dt >= '2024-01-01' AND dt < '2024-02-01'，可利用索引。避免 WHERE MONTH(dt) = 1，这会导致索引失效。

小结

• 生日、历史日期用 DATE；纯时间用 TIME
• 不需要时区转换的未来时间用 DATETIME
• 需要时区自动转换、或需要自动更新机制用 TIMESTAMP
• 绝不用字符串存储日期，会失去日期运算和索引优化的能力

下一章引子：不同数值类型和字符串类型之间经常需要相互转换，MySQL 提供了丰富的类型转换机制。