存储日期，该如何抉择呢

前言

假设现在有一个需求，需要设计一个在线机票订票系统的数据库，此时已经完成了大部分字段的设计，可能如下:

CREATE TABLE flights (   
    flight_id INT AUTO_INCREMENT PRIMARY KEY,   
    flight_number VARCHAR(10),   
    departure_airport_code VARCHAR(3),   
    arrival_airport_code VARCHAR(3),   
    price DECIMAL(10, 2) 
); 

这里还需要存储飞机航班的起飞时间和降落时间，此时需要新增departure_time 和 arrival_time 两个字段来存储。而时间的存储有多种选择:

• 字符串类型 (VARCHAR 或 CHAR): 将时间存储为格式化的字符串，如 “2023-04-25 14:30:00”。
• 整数类型 (INT 或 BIGINT): 使用 Unix 时间戳存储时间，它表示自 1970-01-01 00:00:00 UTC 至今的秒数。
• 原生日期时间类型 (DATETIME 或 TIMESTAMP): 使用数据库提供的专门用于日期和时间的数据类型。

这里我们该如何选择，才能让数据库在实现需求的同时，也保证数据库的高性能呢? 下面我们先充分了解下所有可能的选择，在这个基础上再来对比比较，从而选出最为合适的类型。

时间类型介绍

DATE

在 MySQL 中，DATE 类型用于表示日期值，不包含时间部分。DATE 类型的值格式为 ‘YYYY-MM-DD’，其中：

• YYYY 表示年份，占 4 位数字。
• MM 表示月份，占 2 位数字，范围从 01 到 12。
• DD 表示日，占 2 位数字，范围从 01 到月份对应的最大天数（例如，1月为 31，2月通常为 28 或 29，依此类推）。

DATE 类型的值范围是从 1000-01-01 到 9999-12-31，所以它可以覆盖公历中的几乎所有日期。
在实际使用时，可以用 DATE 类型来存储任何需要日期但不需要时间信息的数据，例如生日、节日、事件发生的日期等。

以下是 DATE 类型的示例，创建一个包含 DATE 类型列的表：

CREATE TABLE events (     
    id INT,     
    event_name VARCHAR(100),     
    event_date DATE 
); 

向表中插入一个日期值：

INSERT INTO events (id, event_name, event_date) VALUES (1, 'New Year', '2024-01-01'); 

查询 DATE 类型的列，数据正常返回，如下:

mysql> SELECT * FROM events WHERE event_date >= '2024-01-01'; 
+------+------------+------------+
| id   | event_name | event_date |
+------+------------+------------+
|    1 | New Year   | 2023-01-01 |
+------+------------+------------+

此外，DATE 类型会对传入的数据进行检查，如果存储的日期不在1000-01-01 到 9999-12-31之间，此时将会报错，不允许其写入，能过预防异常数据的插入:

mysql> INSERT INTO events (id, event_name, event_date) VALUES (1, 'New Year', '12023-01-01'); 
ERROR 1292 (22007): Incorrect date value: '12023-01-01' for column 'event_date' at row 1

在存储方面，DATE 类型的每个值在 MySQL 中占用 4 个字节。这种存储方式比存储相同日期的字符串形式更为高效，同时也便于快速的日期计算和比较。

DATETIME

对于 DATE 类型，只存储日期信息，但是如果需要更高精度的时间，DATE 类型就不能满足需求，此时可以考虑使用DATETIME 类型来存储数据。
在 MySQL 中，DATETIME 类型用于存储日期和时间信息。它可以记录年、月、日、时、分、秒，格式为 YYYY-MM-DD HH:MM:SS，其中：

• YYYY 表示四位数字的年份。
• MM 表示两位数字的月份，范围是 01 到 12。
• DD 表示两位数字的日，范围是 01 到月份对应的最大天数。
• HH 表示两位数字的小时，以 24 小时制表示，范围是 00 到 23。
• MM 表示两位数字的分钟，范围是 00 到 59。
• SS 表示两位数字的秒，范围是 00 到 59。

DATETIME 类型的值范围是从 1000-01-01 00:00:00 到 9999-12-31 23:59:59，可以精确到秒。下面举一个例子说明一下，创建一个包含 DATETIME 类型列的表：

CREATE TABLE appointments (     
    id INT,     
    description VARCHAR(100),    
    start_time DATETIME,     
    end_time DATETIME 
); 

向表中插入一个日期和时间值：

INSERT INTO appointments (id, description, start_time, end_time) VALUES (1, 'Meeting', '2023-04-12 14:00:00', '2023-04-12 15:00:00'); 

查询 DATETIME 类型的列，此时数据正常返回，如下：

mysql> SELECT * FROM appointments WHERE start_time BETWEEN '2023-04-01 00:00:00' AND '2023-04-30 23:59:59';
+------+-------------+---------------------+---------------------+
| id   | description | start_time          | end_time            |
+------+-------------+---------------------+---------------------+
|    1 | Meeting     | 2023-04-12 14:00:00 | 2023-04-12 15:00:00 |
+------+-------------+---------------------+---------------------+

DATETIME 类型同样会对传入的数据进行校验，如果数据不是合法的时间，亦或者不在1000-01-01 00:00:00 到 9999-12-31 23:59:59 范围内，此时也会报错，示例如下:

mysql> INSERT INTO appointments (id, description, start_time, end_time) VALUES (2, 'Meeting', '2023-04-12 14:00:00', '2023-04-12 25:00:00');
ERROR 1292 (22007): Incorrect datetime value: '2023-04-12 25:00:00' for column 'end_time' at row 1

在存储方面，DATETIME 类型的每个值在 MySQL 中占用 8 个字节。这比存储相同的日期和时间信息的字符串更为高效，并允许进行快速的日期和时间计算及比较。

TIMESTAMP

在某些情况下，我们想要在记录中增加一个记录更新时间，当记录更新时，这个列的值能够自动更新为当前的日期和时间，DATE 和 DATETIME并没有这种功能，此时就可以使用TIMESTAMP类型。

在 MySQL 中，TIMESTAMP 类型用于存储日期和时间信息，TIMESTAMP 类型的格式与 DATETIME 类型相同，为 YYYY-MM-DD HH:MM:SS，与DATETIME 类型类似：

• YYYY 表示四位数字的年份。
• MM 表示两位数字的月份，范围是 01 到 12。
• DD 表示两位数字的日，范围是 01 到月份对应的最大天数。
• HH 表示两位数字的小时，以 24 小时制表示，范围是 00 到 23。
• MM 表示两位数字的分钟，范围是 00 到 59。
• SS 表示两位数字的秒，范围是 00 到 59。

TIMESTAMP 类型的值范围是从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。TIMESTAMP 类型的值实际上是存储为自 1970-01-01 00:00:00 UTC 以来的秒数（也称为 Unix 时间戳），并且在检索或显示时根据当前机器的时区设置转换为本地时间。

TIMESTAMP 类型的特殊属性是其能够根据表结构的定义，自动设置和更新时间戳，也就是上面我们提到自动更新功能，这里我们创建一个表来验证一下:

CREATE TABLE user_actions (     
    id INT,     
    action VARCHAR(100),     
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 
); 

在user_actions 表中的 action_time 字段中，我们设置默认值为 CURRENT_TIMESTAMP，同时更新数据时，会自动更新action_time 字段的值为 CURRENT_TIMESTAMP, 下面我们通过一个 INSERT 和 UPDATE 语句来证明一下。

当插入新记录时，MySQL 会自动将当前的日期和时间作为值插入，如下:

mysql> INSERT INTO user_actions (id, action) VALUES (1, 'Login');
Query OK, 1 row affected (0.02 sec)

mysql> select * from user_actions;
+------+--------+---------------------+
| id   | action | action_time         |
+------+--------+---------------------+
|    1 | Login  | 2024-03-04 20:50:56 |
+------+--------+---------------------+
1 row in set (0.02 sec)

可以看到，在执行 INSERT 语句时，我们并没有指定 action_time 的值，但是查询出来的数据与与我插入数据的时间是一致的，说明MySQL确实是会自动将当前的日期和时间作为值插入。

其次，如果 TIMESTAMP 列在表定义中有自动更新的属性，当记录更新时，这个列的值会自动更新为当前的日期和时间，下面我们通过更新上面插入的记录来进行说明:

mysql> update user_actions set action = 'logout' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user_actions;
+------+--------+---------------------+
| id   | action | action_time         |
+------+--------+---------------------+
|    1 | logout | 2024-03-04 20:54:27 |
+------+--------+---------------------+
1 row in set (0.02 sec)

可以看到，我们执行update 语句时并没有指定action_time的值，在执行完update 语句后，这个列的值会自动更新为当前的日期和时间。

除此之外，TIMESTAMP 还有一个特点是其返回的时间是带有时区特性的，这里也举个例子来说明一下:

首先执行下面命令，将时区设置为+08:00 时区:

mysql> SET time_zone = '+08:00';  
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | +08:00 |
+---------------+--------+
1 row in set (0.02 sec)

此时往前面的user_actions 插入数据，不指定 action_time 的值，默认使用当前时间:

mysql> INSERT INTO user_actions (id, action) VALUES (1, 'test');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_actions;
+------+--------+---------------------+
| id   | action | action_time         |
+------+--------+---------------------+
|    1 | test   | 2024-03-05 08:11:28 |
+------+--------+---------------------+
1 row in set (0.02 sec)

此时 mysql 存储action_time 其实是带有时区属性的，为+08:00 时区的2024-03-05 08:11:28，下面我们修改服务器时区为-04:00，然后看查询user_actions 表会返回什么数据:

mysql> SET time_zone = '-04:00'; 
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW VARIABLES LIKE 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | -04:00 |
+---------------+--------+
1 row in set (0.02 sec)

mysql> select * from user_actions;
+------+--------+---------------------+
| id   | action | action_time         |
+------+--------+---------------------+
|    1 | Login  | 2024-03-04 20:11:28 |
+------+--------+---------------------+
1 row in set (0.01 sec)

可以看到，返回的action_time的值已经从2024-03-05 08:11:28 变成了 2024-03-04 20:11:28, 往前提前了12个小时，与时区的变化完全一致，从而能够证明 TIMESTAMP 返回的时间其实是带有时区属性的。

其实 TIMESTAMP 其实存储的是自 1970-01-01 00:00:00 UTC 以来的秒数(也称为 Unix 时间戳)， 返回数据时，MySQL 服务器会根据其配置的时区设置将这个 Unix 时间戳转换为相应的时间，然后返回。

这里需要注意的一个点，因为 TIMESTAMP 存储的是32位的时间戳，所以到2038-01-19 03:14:08 UTC 之后，传统的 32 位 Unix 时间戳将溢出，导致时间无法正确表示，所以如果使用 TIMESTAMP 来存储时间需要注意到这个问题。

非原生类型存储日期

字符串类型

除了原生的日期时间类型外，有时候有人可能也会使用字符串类型（如 VARCHAR 或 CHAR）来存储日期和时间信息，比如下面的定义:

CREATE TABLE events (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    name VARCHAR(255),     
    event_datetime VARCHAR(19) -- 用于存储日期时间的字符串 
); 

然后执行下面的插入语句，也可以实现日期时间的存储:

INSERT INTO events (name, event_datetime) VALUES ('test', '2024-03-04 12:00:00'); 

但是通常是使用字符串类型来存储日期和时间，原生的时间类型是为了处理日期和时间而优化的，它们在存储效率、性能和功能上通常都要优于字符串类型。

下面可以通过定义两张表，往其中插入一批数据，比较二者的性能性能差距，表的定义如下所示:

-- 使用 TIMESTAMP 类型的表
CREATE TABLE events_timestamp (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    event_datetime TIMESTAMP 
);  

-- 使用 VARCHAR 类型的表
CREATE TABLE events_varchar (     
    id INT AUTO_INCREMENT PRIMARY KEY,     
    event_datetime VARCHAR(19) 
); 

使用存储过程分别往这两张表插入100w条数据。首先往events_timestamp插入100w条数据:

DELIMITER //

CREATE PROCEDURE InsertEventsTimestamp()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO events_timestamp (event_datetime) VALUES (NOW());
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

再往events_varchar插入100w条数据:

DELIMITER //

CREATE PROCEDURE InsertEventsVarchar()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE current_datetime VARCHAR(19);
  WHILE i < 1000000 DO
    -- 生成当前的日期时间字符串
    SET current_datetime = DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
    INSERT INTO events_varchar (event_datetime) VALUES (current_datetime);
    SET i = i + 1;
  END WHILE;
END //

DELIMITER ;

此时 events_timestamp 和 events_varchar 表中都有100w条数据。

我们下面通过执行查询语句来比较 TIMESTAMP/DATETIME 类型和 CHAR/VARCHAR 类型在存储效率、性能和功能上的差异。

首先通过这个SQL查看 events_timestamp 和 events_varchar 两张表占用的磁盘的大小:

 SELECT  table_name AS 'Table',   round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = 'test' AND table_name in ('events_varchar', 'events_timestamp');
+------------------+------------+
| Table            | Size in MB |
+------------------+------------+
| events_timestamp |      49.09 |
| events_varchar   |      86.14 |
+------------------+------------+
2 rows in set (0.01 sec)

可以看到，events_varchar 占用的磁盘空间比events_timestamp 多了一倍。磁盘空间的变大，查询数据时也会增加磁盘 I/O 的操作，占用更多的内存，降低mysql的查询性能。

接着我们来比较二者在查询效率上的差距，为了效果更为明显，这里不加索引从而让查询进行全表扫描，看看两边性能上的差距，这里我会开启profile，方便查看各阶段的耗时:

SET profiling = 1; 

首先是对 events_timestamp 进行查询，具体SQL如下，只查询一行数据，同时让其进行排序:

mysql> select * from events_timestamp order by event_datetime desc limit 100000,1;
+--------+---------------------+
| id     | event_datetime      |
+--------+---------------------+
| 900154 | 2024-03-05 21:07:18 |
+--------+---------------------+
1 row in set (0.36 sec)

查询该SQL各阶段的执行耗时，看看events_timestamp的效果怎么样:

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000011 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000018 |
| init                 | 0.000025 |
| System lock          | 0.000006 |
| optimizing           | 0.000005 |
| statistics           | 0.000025 |
| preparing            | 0.000033 |
| Sorting result       | 0.000007 |
| executing            | 0.000003 |
| Sending data         | 0.000007 |
| Creating sort index  | 0.277743 |
| end                  | 0.000023 |
| query end            | 0.000008 |
| closing tables       | 0.000008 |
| freeing items        | 0.000042 |
| cleaning up          | 0.000012 |
+----------------------+----------+
18 rows in set, 1 warning (0.01 sec)

这里重点关注下 Creating sort index，这个阶段是MySQL 正在创建一个内部的排序索引来临时存储排序操作的结果，使用 TIMESTAMP 在无索引情况下的耗时为0.277s。

下面对 events_varchar 进行查询，全部条件保持一致，避免其他因素的干扰:

mysql> select * from events_varchar order by event_datetime desc limit 100000,1;
+--------+---------------------+
| id     | event_datetime      |
+--------+---------------------+
| 899993 | 2024-03-05 22:21:10 |
+--------+---------------------+
1 row in set (0.54 sec)

查询该SQL各阶段的执行耗时，看看varchar 存储时间的效果:

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000070 |
| checking permissions | 0.000010 |
| checking permissions | 0.000004 |
| Opening tables       | 0.000016 |
| init                 | 0.000025 |
| System lock          | 0.000007 |
| optimizing           | 0.000005 |
| statistics           | 0.000023 |
| preparing            | 0.000017 |
| Sorting result       | 0.000005 |
| executing            | 0.000002 |
| Sending data         | 0.000006 |
| Creating sort index  | 0.526720 |
| end                  | 0.000023 |
| query end            | 0.000008 |
| closing tables       | 0.000008 |
| freeing items        | 0.000041 |
| cleaning up          | 0.000011 |
+----------------------+----------+

可以看到，MySQL 在Creating sort index 这个阶段的耗时为0.52s，相比于TIMESTAMP 类型的耗时多了差不多一倍，说明在使用VARCHAR 存储时间下，比较排序是更加耗费资源的。
由此可以证明 VARCHAR 不管是在存储时间方面，还是在查询性能方面，都比原生时间类型 TIMESTAMP 表现更差一点。 在设计数据库和应用时，应优先考虑使用 DATETIME、TIMESTAMP 或其他适合的时间类型。而不是使用 VARCHAR 来存储时间。

整形

在实际开发过程中，我们也会使用整数类型（通常是32位或64位的整数）来存储时间戳。下面我们比较整数类型和原生时间类型，看看这二者在存储效率，查询性能，功能支持的区别，从而能够做出更好的选择。

在存储效率方面，二者其实没有太大的区别。 整数类型（如 INT 和 BIGINT）通常占用固定的存储空间（INT 为 4 字节，BIGINT 为 8 字节）。而对于原生时间类型（如 DATETIME 和 TIMESTAMP）,在 MySQL 中，DATETIME 占用 8 字节，TIMESTAMP 占用 4 字节。

对于查询性能，整数类型的比较通常很快，因为它们是CPU原生支持的操作。如果查询不涉及到时间日期的特定函数，使用整数可能会提供更好的性能。但是二者的性能差距可以忽略不计，具体验证可以参考上面字符串类型性能比较的过程，这里就不再重复了。

对于功能支持方面，原生时间类型优化了日期和时间的相关计算。在使用日期时间函数进行复杂查询时，如日期间隔计算、日期格式化输出等，原生时间类型通常会提供更好的性能和方便的函数支持。

比如下面这个例子，我们创建一个events表:

CREATE TABLE events (   
    event_id INT AUTO_INCREMENT PRIMARY KEY,   
    event_name VARCHAR(255),   
    start_time DATETIME,   
    end_time DATETIME,   
    location VARCHAR(255) 
 ); 

events 表中包含两个 DATETIME 类型的列 start_time 和 end_time，我们想要计算每个事件的持续时间。此时因为是使用原生时间类型，我们可以直接使用 TIMEDIFF 函数来获取两个时间点之间的差值：

SELECT   
    event_id,   
    start_time,   
    end_time,   
    TIMEDIFF(end_time, start_time) AS duration 
 FROM   
    events; 

相比之下，如果我们使用整数类型存储时间戳，同样的操作通常需要更复杂的转换和计算，这不仅增加了代码的复杂性，而且可能影响性能。

综上所述，整形类型存储时间戳相比于原生时间类型，在存储效率，查询性能并没有突出的优势，相反还少了一些日期时间函数的操作，以及整形时间戳的可读性也比较差，所以还是建议使用原生时间类型而不是用整形来存储。

怎么选择

在MySQL中存储日期和时间，虽然有多种选择，如DATE,DATETIME,TIMESTAMP,但是对于某一个业务场景来说，往往只有某一种类型在满足时间精度要求的前提下，在存储效率，查询性能上表现得更为优秀。
下面我们来展示下几种原生时间类型在存储效率，查询性能，时间精度表现上的差异:

类型/比较维度	DATE	DATETIME	TIMESTAMP
时间精度	包含日期	包含日期和时间	包含日期和时间，以及时区信息
存储效率	4字节	8字节	4字节
时间范围	1000-01-01 到 9999-12-31	1000-01-01 00:00:00 到 9999-12-31 23:59:59	1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
查询性能	基于相同	基于相同	基于相同

可以看到，如果只需要存储日期，DATE 是最好的选择，其存储效率最高；如果需要日期和时间，则 DATETIME 和 TIMESTAMP 是常用选项，这两者相比TIMESTAMP 是存储效率更高的选择，但是其能表示的时间范围有限。而DATETIME 则没有这个限制。

下面我们再来综合比较下，看看两种非原生类型(字符串和整数)，相比于原生数据类型在存储，执行效率下的表现:

类型/比较维度	字符串	整形
存储效率	较差，占用更多的存储空间	与原生时间类型基本相同
查询性能	比较和排序效率较差	比较和排序效率高
功能支持	无，日期计算等均需自己实现	无，日期计算等均需自己实现
时间范围	无限制	64位整形无限制

从上可以看出，在大部分情况下应该避免使用字符串来保存时间，而是应该基于业务需求，使用最为合适的原生时间类型。

如果不需要MySQL提供的日期和时间函数的话，使用整形来存储时间也是可以的。

回到上面数据库设计的问题上，我会这么考虑时间类型的定义:

• 字符串类型: 从上面对比的结果可以看到，由于其存储效率，性能等问题，基本上不会选择，这里不做考虑
• 整形: 在该场景下，有时候需要使用时间函数来计算航班总时程，需要自己实现，也不做考虑
• DATE: 不满足时间精度的要求，不考虑
• TIMESTAMP: 时间范围有限，不过存储效率高，只需要4个字节
• DATETIME: 不会受到时间范围的限制，不过存储效率相对低一点，需要8个字节

综合考虑之下，我还是选择 DATETIME 类型，从而支持更大的时间范围，最终表结构定义如下:

CREATE TABLE flights (   
    flight_id INT AUTO_INCREMENT PRIMARY KEY,   
    flight_number VARCHAR(10),   
    departure_airport_code VARCHAR(3),   
    arrival_airport_code VARCHAR(3),   
    departure_time DATETIME not null,   
    arrival_time DATETIME not null,   
    price DECIMAL(10, 2) 
); 

总结

在时间类型存储上，MySQL提供了多种时间类型，如 DATE, DATETIME, TIMESTAMP，满足各种时间精度的需求；在某些场景下，也可以使用字符串或者整形来存储时间。

本文详细介绍了各种时间类型，同时在时间精度，存储范围，执行效率等维度上对其进行了比较，展示了其长处和相对应的缺点。

从而能够在数据库设计时，在时间字段的类型作出更准确，更高效的选择。

本文由mdnice多平台发布