首页 > 编程笔记 > MySQL笔记

MySQL日期类型（DATETIME、DATE、TIMESTAMP、TIME和YEAR）

MySQL 中有多种表示日期的数据类型，主要有 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

例如，当只记录年信息的时候，可以只使用 YEAR 类型，而没有必要使用 DATE。每一个类型都有合法的取值范围，当指定确实不合法的值时，系统将“零”值插入到数据库中。

本节将介绍 MySQL 日期和时间类型的使用方法，下表列出了 MySQL 中的日期与时间类型。

表 1 日期与时间数据类型
类型名称 日期格式 日期范围 存储需求
YEAR YYYY 1901-2155 1 字节
TIME HH:MM:SS -838:59:59~838:59:59 3 字节
DATE YYYY-MM-DD 1000-01-01~9999-12-31 3 字节
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~9999-12-31 23:59:59 8 字节
TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC 4 字节

1、YEAR

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 字节。

指定 YEAR 值的格式有很多种，如下所示：
1) 以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 1901~2155。输入格式为“YYYY”或者 YYYY。例如，输入'2010'或 2010，插入到数据库的值均为 2010。

2) 以 2 位字符串格式表示的 YEAR，范围为 00~99。00~69 和 70~99 范围的值分别被转换为 2000~2069 和 1970~1999 范围的 YEAR 值。0 与 00 的作用相同。插入超过取值范围的值将被转换为 2000。

3) 以 2 位数字表示的 YEAR，范围为 1~99。1~69 和 70~99 范围的值分别被转换为 2001~2069 和 1970~1999 范围的 YEAR 值。注意：这里的 0 值将被转换为 0000，而不是 2000。

两位整数范围与两位字符串范围稍有不同。例如：插入 2000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 2000。只有使用字符串格式的'0'或'00'，才可以被正确地解释为 2000。非法 YEAR 值将被转换为 0000。

【实例】创建数据表 tmp3，定义数据类型为 YEAR 的字段 y，向表中插入值 2010、'2010'、'2166'，SQL 语句如下：
① 首先创建表 tmp3： 
CREATE TABLE tmp3(y YEAR);
② 向表中插入数据： 
mysql> INSERT INTO tmp3 values(2010),('2010');
③ 再次向表中插入数据： 
mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1
④ 语句执行之后，MySQL 给出了一条错误提示，使用SHOW查看错误信息： 
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Error   | 1264 | Out of range value for column 'y' at row 1;  |
+---------+------+----------------------------------------------+
可以看到，插入的第 3 个值'2166'超过了 YEAR 类型的取值范围，此时不能正常执行插入操作，查看结果： 
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
+------+
由结果可以看到，当插入值为数值类型的 2010 或者字符串类型的'2010'时，都能正确地储存到数据库中；而当插入值'2166'时，由于超出了 YEAR 类型的取值范围，因此不能插入值。

【实例】向 tmp3 表中 y 字段插入 2 位字符串表示的 YEAR 值，分别为'0'、'00'、'77'和'10'，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp3;
② 向表中插入数据： 
INSERT INTO tmp3 values('0'),('00'),('77'),('10');
③ 查看结果： 
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
由结果可以看到，字符串 '0' 和 '00' 的作用相同，分别都转换成了 2000 年；'77' 转换为 1977；'10' 转换为 2010。

【实例】向 tmp3 表中 y 字段插入 2 位数字表示的 YEAR 值，分别为 0、78 和 11，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp3;
② 向表中插入数据： 
INSERT INTO tmp3 values(0),(78),(11);
③ 查看结果： 
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 0000 |
| 1978 |
| 2011 |
+------+
由结果可以看到，0 被转换为 0000，78 被转换为 1978，11 被转换为 2011。

2、TIME

TIME 类型用在只需要时间信息的值，在存储时需要 3 字节，格式为“HH:MM:SS”。其中，HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为：-838:59:59~838:59:59，小时部分的取值范围会如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可以用于表示某个事件过去的时间或两个事件之间的时间间隔（可以大于 24 小时，或者为负）。

指定 TIME 值的格式有很多种，包括：
1) “D HH:MM:SS” 格式的字符串。可以使用下面任何一种“非严格”的语法：“HH:MM:SS”“HH:MM”“D HH:MM”“D HH”或“SS”。这里的 D 表示日，可以取 0~24 之间的值。在插入数据库时，D 被转换为小时保存，格式为“D*24 + HH”。

2) “HHMMSS”格式的、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如：'101112'被理解为 10:11:12，但'109712'是不合法的（它有一个没有意义的分钟部分），存储时将变为 00:00:00。

为 TIME 列分配简写值时应该注意，如果没有冒号，MySQL 解释值时会假定最右边的两位表示秒（MySQL 解释 TIME 值为过去的时间而非当天的时间）。例如，读者可能认为'1112'和 1112 表示 11:12:00（11 点 12 分），但 MySQL 将它们解释为 00:11:12（11 分 12 秒）。同样地，'12' 和 12 被解释为 00:00:12。相反地，如果 TIME 值中使用冒号则肯定被看作当天的时间。也就是说，'11:12' 表示 11:12:00，而不是 00:11:12。

【实例】创建数据表 tmp4，定义数据类型为 TIME 的字段 t，向表中插入值 '10:05:05'、'23:23'，'2 10:10'、'3 02'、'10'，SQL 语句如下：
① 创建表 tmp4： 
CREATE TABLE tmp4(t TIME);
② 向表中插入数据： 
mysql> INSERT INTO tmp4 values('10:05:05'), ('23:23'), ('2 10:10'), ('3 02'),('10');
查看结果： 
mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:05:05 |
| 23:23:00 |
| 58:10:00 |
| 74:00:00 |
| 00:00:10 |
+----------+
由结果可以看到，'10:05:05' 被转换为 10:05:05；'23:23' 被转换为 23:23:00；'2 10:10' 被转换为 58:10:00，'3 02' 被转换为 74:00:00；'10' 被转换成 00:00:10。

在使用“D HH”格式时，小时一定要使用双位数值，如果是小于 10 的小时数，应在前面加 0。

【实例】向表 tmp4 中插入值'101112'、111213、'0'、107010，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp4;
② 向表中插入数据： 
mysql>INSERT INTO tmp4 values('101112'),(111213),( '0');
③ 再向表中插入数据： 
mysql>INSERT I?NTO tmp4 values ( 107010);
ERROR 1292 (22007): Incorrect time value: '107010' for column 't' at row 1
④ 可以看到，在插入数据时，MySQL 给出了一个错误提示信息，使用“SHOW WARNINGS;”查看错误信息，如下所示： 
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Error   | 1292 |Incorrect time value: '107010' for column 't' at row 1|
+---------+------+------------------------------------------------------+
可以看到，第二次在插入记录的时候，数据超出了范围，原因是 107010 的分钟部分超过了60（分钟部分是不会超过 60 的）。结果如下： 
mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 10:11:12 |
| 11:12:13 |
| 00:00:00 |
+----------+
由结果可以看到，'101112' 被转换为 10:11:12；111213 被转换为 11:12:13；'0' 被转换为 00:00:00；因为 107010 是不合法的值，所以不能被插入。

也可以使用系统日期函数向 TIME 字段列插入值。

【实例】向 tmp4 表中插入系统当前时间，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp4;
② 向表中插入数据： 
mysql> INSERT INTO tmp4 values (CURRENT_TIME) ,(NOW());
查看结果： 
mysql> SELECT * FROM tmp4;
+----------+
| t        |
+----------+
| 08:43:51 |
| 08:43:51 |
+----------+
由结果可以看到，获取系统当前的日期时间插入到 TIME 类型的列 t，因为读者输入语句的时间不确定，所以获取的值可能与这里的不同，但都是系统当前的日期时间值。

3、DATE类型

DATE 类型用在仅需要日期值时，没有时间部分，在存储时需要 3 字节。日期格式为“YYYY-MM-DD”。其中，YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可：

1) 以“YYYY-MM-DD”或者“YYYYMMDD”字符串格式表示的日期，取值范围为 1000-01-01~9999-12-3。例如，输入“2012-12-31”或者“20121231”，插入数据库的日期都为 2012-12-31。

2) 以“YY-MM-DD”或者“YYMMDD”字符串格式表示的日期，在这里 YY 表示两位的年值。包含两位年值的日期会令人模糊，因为不知道世纪。MySQL 使用以下规则解释两位年值：00~69 范围的年值转换为 2000~2069；70~99 范围的年值转换为 1970~1999。例如，输入“12-12-31”，插入数据库的日期为 2012-12-31；输入“981231”，插入数据的日期为 1998-12-31。

3) 以 YY-MM-DD 或者 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000~2069，70~99 范围的年值转换为 1970~1999。例如，输入 12-12-31 插入数据库的日期为 2012-12-31；输入 981231，插入数据的日期为 1998-12-31。

4) 使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

【实例】创建数据表 tmp5，定义数据类型为 DATE 的字段 d，向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期，SQL 语句如下：
① 创建表tmp5： 
MySQL> CREATE TABLE tmp5(d DATE);
② 向表中插入“YYYY-MM-DD”和“YYYYMMDD”字符串格式日期： 
MySQL> INSERT INTO tmp5 values('1998-08-08'),('19980808'),('20101010');
查看插入结果： 
MySQL> SELECT * FROM tmp5;
+------------+
| d          |
+------------+
| 1998-08-08 |
| 1998-08-08 |
| 2010-10-10 |
+------------+
可以看到，各个不同类型的日期值都正确地插入到了数据表中。

【实例】向 tmp5 表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp5;
② 向表中插入“YY-MM-DD”和“YYMMDD”字符串格式日期： 
mysql> INSERT INTO tmp5 values ('99-09-09'),( '990909'), ('000101') ,('111111');
查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 1999-09-09  |
| 1999-09-09  |
| 2000-01-01  |
| 2011-11-11  |
+-------------+

【实例】向 tmp5 表中插入 YYYYMMDD 和 YYMMDD 数字格式日期，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp5;
② 向表中插入 YYYYMMDD 和 YYMMDD 数字格式日期： 
mysql> INSERT INTO tmp5 values (19990909),(990909), (000101) ,(111111);
查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 1999-09-09  |
| 1999-09-09  |
| 2000-01-01  |
| 2011-11-11  |
+-------------+

【实例】向 tmp5 表中插入系统当前日期，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp5;
② 向表中插入系统当前日期： 
mysql> INSERT INTO tmp5 values( CURRENT_DATE() ),( NOW() );
③ 查看插入结果： 
mysql> SELECT * FROM tmp5;
+-------------+
| d           |
+-------------+
| 2022-03-09  |
| 2022-03-09  |
+-------------+
CURRENT_DATE 只返回当前日期值，不包括时间部分；NOW() 函数返回日期和时间值，在保存到数据库时，只保留了其日期部分。

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，“98-11-31”、“98.11.31”、“98/11/31”和“98@11@31”是等价的，这些值都可以正确地插入到数据库中。

4、DATETIME

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 字节。日期格式为 YYYY-MM-DD HH:MM:SS。其中，YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可。
1) 以“YYYY-MM-DD HH:MM:SS”或者“YYYYMMDDHHMMSS”字符串格式表示的值，取值范围为1000-01-01 00:00:00~9999-12-3 23:59:59。例如，输入“2012-12-31 05:05:05”或者“20121231050505”，插入数据库的 DATETIME 值都为 2012-12-31 05: 05: 05。

2) 以“YY-MM-DD HH:MM:SS”或者“YYMMDDHHMMSS”字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，00~69 范围的年值转换为 2000~2069，70~99 范围的年值转换为 1970~1999。例如，输入“12-12-31 05:05:05”，插入数据库的 DATETIME 为 2012-12-31 05:05:05；输入“980505050505”，插入数据库的 DATETIME 为 1998-05-05 05: 05: 05。

3) 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入“20121231050505”，插入数据库的 DATETIME 为 2012-12-31 05:05:05；输入“981231050505”，插入数据的 DATETIME 为 1998-12-31 05: 05: 05。

【实例】创建数据表 tmp6，定义数据类型为 DATETIME 的字段 dt，向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”字符串格式日期和时间值，SQL 语句如下：
① 创建表 tmp6： 
CREATE TABLE tmp6(dt DATETIME);
② 向表中插入“YYYY-MM-DD HH:MM:SS”和“YYYYMMDDHHMMSS”格式日期： 
mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);
查看插入结果： 
mysql> SELECT * FROM tmp6;
+------------------------+
| dt                     |
+------------------------+
| 1998-08-08 08:08:08    |
| 1998-08-08 08:08:08    |
| 2010-10-10 10:10:10    |
+------------------------+
可以看到，各个不同类型的日期值都正确地插入到了数据表中。

【实例】向 tmp6 表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”字符串格式日期和时间值，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp6;
② 向表中插入“YY-MM-DD HH:MM:SS”和“YYMMDDHHMMSS”格式日期： 
mysql> INSERT INTO tmp6 values('99-09-09 09:09:09'),('990909090909'),('101010101010');
查看插入结果： 
mysql> SELECT * FROM tmp6;
+-------------------------+
| dt                   |
+-------------------------+
| 1999-09-09 09:09:09     |
| 1999-09-09 09:09:09     |
| 2010-10-10 10:10:10     |
+-------------------------+

【实例】向 tmp6 表中插入“YYYYMMDDHHMMSS”和“YYMMDDHHMMSS”数字格式日期和时间值，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp6;
② 向表中插入“YYYYMMDDHHMMSS”和“YYMMDDHHMMSS”数字格式日期和时间： 
mysql> INSERT INTO tmp6 values(19990909090909), (101010101010);
查看插入结果： 
mysql> SELECT * FROM tmp6;
+--------------------------+
| dt                       |
+--------------------------+
| 1999-09-09 09:09:09      |
| 2010-10-10 10:10:10      |
+-------------------------+

【实例】向 tmp6 表中插入系统当前日期和时间值，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp6;
② 向表中插入系统当前日期： 
mysql> INSERT INTO tmp6 values( NOW() );
查看插入结果： 
mysql> SELECT * FROM tmp6;
+-------------------------+
| dt                      |
+-------------------------+
| 2022-03-15 17:07:30     |
+-------------------------+
NOW() 函数返回当前系统的日期和时间值，格式为“YYYY-MM-DD HH:MM:SS”。

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分或时间部分之间的间隔符。例如，98-12-31 11:30:45、98.12.31 11+30+45、98/12/31 11*30*45 和 98@12@31 11^30^45 是等价的，这些值都可以正确地插入数据库。

5、TIMESTAMP

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为“YYYY-MM-DD HH:MM:SS”，在存储时需要 4 字节。

TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC。其中，UTC（Coordinated Universal Time）为世界标准时间，因此在插入数据时，要保证在合法的取值范围内。

【实例】创建数据表 tmp7，定义数据类型为 TIMESTAMP 的字段 ts，向表中插入值 19950101010101、950505050505、1996-02-02 02:02:02、97@03@03 03@03@03、121212121212、NOW()，SQL 语句如下：
① 创建数据表 tmp7： 
CREATE TABLE tmp7(ts TIMESTAMP);
② 向表中插入数据： 
INSERT INTO tmp7 values ('19950101010101'),
('950505050505'),
('1996-02-02 02:02:02'),
('97@03@03 03@03@03'),
(121212121212),
( NOW() );
查看插入结果： 
mysql>SELECT * FROM tmp7;
+---------------------+
| ts                  |
+---------------------+
| 1995-01-01 01:01:01 |
| 1995-05-05 05:05:05 |
| 1996-02-02 02:02:02 |
| 1997-03-03 03:03:03 |
| 2012-12-12 12:12:12 |
| 2022-03-09 17:08:25 |
+---------------------+
由结果可以看到，“19950101010101”被转换为 1995-01-01 01:01:01；“950505050505”被转换为 1995-05-05 05:05:05；“1996-02-02 02:02:02”被转换为 1996-02-02 02:02:02；“97@03@03 03@03@03”被转换为 1997-03-03 03:03:03；121212121212 被转换为 2012-12-12 12:12:12；NOW() 被转换为系统当前日期时间 2022-03-09 17:08:25。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别就是：DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。查询时，不同时区显示的时间值是不同的。

【实例】向 tmp7 表中插入当前日期，查看插入值，更改时区为东 10 区，再次查看插入值，SQL 语句如下：
① 删除表中的数据： 
DELETE FROM tmp7;
② 向表中插入系统当前日期： 
mysql> INSERT INTO tmp7
values( NOW() );
③ 查看当前时区下的日期值： 
mysql> SELECT * FROM tmp7;
+-------------------------+
| ts                      |
+-------------------------+
| 2022-03-09 17:12:20     |
+-------------------------+
④ 查询结果为插入时的日期值。我国读者所在时区一般为东 8 区，下面修改当前时区为东 10 区，SQL 语句如下： 
mysql> set time_zone='+10:00';
⑤ 再次查看插入时的日期值： 
mysql> SELECT * FROM tmp7;
+-------------------------+
| ts                   |
+-------------------------+
| 2022-03-09 19:12:20     |
+-------------------------+
由结果可以看到，因为东 10 区时间比东 8 区快 2 个小时，所以查询的结果经过时区转换之后，显示的值增加了 2 小时。类似地，如果时区每减小一个值，则查询显示的日期中的小时数减 1。

如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，那么结果值的时间部分将被设置为“00:00:00”，因为 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，那么结果值的时间部分将被删除，因为 DATE 值未包含时间信息。

相关文章