MySQL 日期时间类型

日期时间类型的选择是很多初学者容易困惑的地方。MySQL 为我们提供了常用的 5 种时间类型：DATE、TIME、DATETIME、TIMESTAMP、YEAR，还有高精度 DATETIME(n) / TIMESTAMP(n) 支持微秒。如下表：

下面将详细介绍。

DATE、TIME、DATETIME

这三个日期类型，分别表示日期（年月日）、时间（时分秒）和日期时间（年月日时分秒），下面通过一个例子演示：

-- 创建数据表
mysql> CREATE TABLE datetime_demo (
    ->     birth_date DATE COMMENT '生日，格式：YYYY-MM-DD',
    ->     work_start TIME COMMENT '上班时间，格式：HH:MM:SS',
    ->     created_at DATETIME COMMENT '创建时间，格式：YYYY-MM-DD HH:MM:SS'
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入数据
mysql> INSERT INTO datetime_demo VALUES (
    ->     '1990-05-20',
    ->     '09:00:00',
    ->     '2023-12-01 14:30:00'
    -> );
Query OK, 1 row affected (0.00 sec)

-- 查询
mysql> SELECT * FROM datetime_demo;
+------------+------------+---------------------+
| birth_date | work_start | created_at          |
+------------+------------+---------------------+
| 1990-05-20 | 09:00:00   | 2023-12-01 14:30:00 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

注意了，DATETIME 是业务中的首选，最通用，它不依赖时区，存什么就展示什么，不会自动转换。并且无 2038 年限制，上限到 9999 年，适合长期业务、历史订单。还支持手动写入任意时间，灵活可控，支持自动更新 create_time DATETIME DEFAULT CURRENT_TIMESTAMP。

TIMESTAMP 的特性

TIMESTAMP 和 DATETIME 看起来很像，但有重要区别，我们先看看如何使用 TIMESTAMP，然后再讨论区别：

-- 创建数据表
mysql> CREATE TABLE timestamp_demo (
    ->     dt DATETIME,
    ->     ts TIMESTAMP
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入数据
mysql> INSERT INTO timestamp_demo VALUES (
    ->     '2023-12-01 14:30:00',
    ->     '2023-12-01 14:30:00'
    -> );
Query OK, 1 row affected (0.00 sec)

-- 查看
mysql> SELECT * FROM timestamp_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2023-12-01 14:30:00 | 2023-12-01 14:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

从效果上面看，上面示例中 DATETIME 和 TIMESTAMP 没有区别，都表示了正确的时间。然而，TIMESTAMP 底层存储 UTC 时间戳（指从 1970-01-01 00:00:00 UTC 起累计的秒 / 毫秒数，与时区无关），查询时会根据当前会话时区自动转换。致命短板是 2038 年溢出，2038 年后数据直接报错，长期项目禁用。但 TIMESTAMP 仅占用 4 字节，存储空间更小，适合下面一些场景：

• 短期临时数据、日志表（生命周期短，不用存几十年）；

• 电商、订单、用户系统严禁做主时间字段。

例如：

-- 查看当前时区
-- 当前采用的是系统时区
mysql> SELECT @@session.time_zone;
+---------------------+
| @@session.time_zone |
+---------------------+
| SYSTEM              |
+---------------------+
1 row in set (0.00 sec)

-- 修改会话时区
SET time_zone = '+8:00';   -- 北京时间
SET time_zone = 'America/New_York';  -- 纽约时间

-- 将时区修改被北京时间
mysql> SET time_zone = '+8:00';
Query OK, 0 rows affected (0.00 sec)

-- 再次查询 timestamp_demo 表
mysql> SELECT * FROM timestamp_demo;
+---------------------+---------------------+
| dt                  | ts                  |
+---------------------+---------------------+
| 2023-12-01 14:30:00 | 2023-12-01 22:30:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

此时就会发现，查询出来的两个日期不一样了，dt 列还是我们设置的日期，而 ts 列的时间加了8小时，这就是 timestamp 自动时区转换的操作。

mysql 广告位

自动更新

当我们向数据表插入数据，或者更新数据表中的数据时，mysql 服务器会自动将当前的系统时间设置到日期字段上，这就是自动更新。实际开发中自动更新非常方便，当我们插入数据时自动为创建时间字段设置时间，更新数据时自动为更新时间字段设置值，不需要通过程序手动设置，非常方便。例如：

-- 创建数据表
mysql> CREATE TABLE auto_timestamp (
    ->     id INT PRIMARY KEY,
    ->     content VARCHAR(100),
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    ->     updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 插入数据
mysql> INSERT INTO auto_timestamp (id, content) VALUES (1, '测试');
Query OK, 1 row affected (0.00 sec)
-- created_at 和 updated_at 自动填充为当前时间

-- 更新数据
mysql> UPDATE auto_timestamp SET content = '修改后' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- updated_at 自动更新为当前时间，created_at 保持不变

-- 查询数据表数据
mysql> select * from auto_timestamp;
+----+-----------+---------------------+---------------------+
| id | content   | created_at          | updated_at          |
+----+-----------+---------------------+---------------------+
|  1 | 修改后    | 2026-07-05 14:47:40 | 2026-07-05 14:47:55 |
+----+-----------+---------------------+---------------------+
1 row in set (0.00 sec)

那么，在 datetime 和 timestamp 之间该如何选择呢？下面可做一些参考：

• DATETIME：

• 需要存储的历史日期（如生日、百年纪念）

• 不需要考虑时区的场景

• 未来的日期（超过2038年）

• TIMESTAMP：

• 记录创建/更新时间（自动更新很方便）

• 多时区应用（自动转换时区）

• 在2038年之前的业务时间

日期时间函数

MySQL提供了丰富的日期时间处理函数，下面通过示例介绍一些常用的便捷函数：

-- 获取当前日期时间
SELECT NOW();           -- 2023-12-01 14:30:00
SELECT CURDATE();       -- 2023-12-01
SELECT CURTIME();       -- 14:30:00
SELECT CURRENT_TIMESTAMP;  -- 同上

-- 日期提取
SELECT YEAR('2023-12-01');      -- 2023
SELECT MONTH('2023-12-01');     -- 12
SELECT DAY('2023-12-01');       -- 1
SELECT DAYNAME('2023-12-01');   -- Friday
SELECT DAYOFWEEK('2023-12-01'); -- 6（周日=1）

-- 日期计算
SELECT DATE_ADD('2023-12-01', INTERVAL 1 DAY);     -- 2023-12-02
SELECT DATE_SUB('2023-12-01', INTERVAL 1 MONTH);   -- 2023-11-01
SELECT DATEDIFF('2023-12-31', '2023-01-01');       -- 364

-- 日期格式化
SELECT DATE_FORMAT('2023-12-01 14:30:00', '%Y年%m月%d日 %H:%i');  -- 2023年12月01日 14:30
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');  -- Friday, December 01, 2023

-- 字符串转日期
SELECT STR_TO_DATE('2023-12-01', '%Y-%m-%d');

-- 获取时间戳
SELECT UNIX_TIMESTAMP();        -- 当前时间戳（秒）
SELECT UNIX_TIMESTAMP('2023-12-01 00:00:00');  -- 指定时间的时间戳
SELECT FROM_UNIXTIME(1701388800);  -- 时间戳转日期

更多函数参考MySQL 函数大全。