关键词

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 值未包含时间信息。

本文链接:http://task.lmcjl.com/news/5164.html

展开阅读全文