5.1.3 日期与时间类型
MySQL中有多种表示日期的数据类型，主要有：DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如，当只记录年信息的时候，可以只是用YEAR类型，而没有必要使用DATE。每一个类型都有合法的取值范围，当指定确实不合法的值时系统将“零”值插入到数据库中。本节将介绍MySQL日期和时间数据类型的使用方法。【表5.4】列出了MySQL中的日期与时间类型。
表5.4 日期/时间数据类型
|
类型名称 |
日期格式 |
日期范围 |
存储需求 |
|
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-3 |
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值，如下：
⑴ 以4位字符串或者四位数字格式表示的YEAR，范围为‘1901’～‘2155’。输入格式为‘YYYY’或者YYYY，例如，输入2010或‘2010’，插入到数据库的值均为2010。
⑵ 以2位字符串格式表示的YEAR，范围为‘00’到‘99’。‘00'～‘69’和‘70’～‘99’范围的值分别被转换为2000～2069和1970～1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为0000。
⑶ 以2位数字表示的YEAR，范围为1～99。1～69和70～99范围的值分别被转换为2001～2069和1970～1999范围的YEAR值。注意：在这里0值将被转换为0000，而不是2000。
【例5.3】创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，‘2010’，‘2166’，SQL语句如下：
首先创建表tmp3：
CREATE TABLE tmp3( y YEAR );
向表中插入数据：
mysql> INSERT INTO tmp3 values(2010),('2010'),('2166');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1
语句执行之后，MySQL给出了一条警告，使用SHOW查看警告信息：
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 't' at row 3 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
可以看到，插入的第三个值2166超过了YEAR类型的取值范围，此时可以正常的执行插入操作，但是插入的结果却发生了变化，查看结果：
mysql> SELECT * FROM tmp3;
+------+
| y |
+------+
| 2010 |
| 2010 |
| 0000 |
+------+
由结果可以看到，当插入值为数值类型的2010或者字符串类型的‘2010’时，都正确的储存到了数据库中，当插入值‘2166’时，超出了YEAR类型的取值范围，因此，插入值为0000。
【例5.4】向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。
【例5.5】向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值，如下：
⑴ ‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”语法：‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里D表示日，可以取0到34之间的值。在插入数据库时，D被转换为小时保存，格式为 “D*24 + HH”。
⑵‘HHMMSS’格式的没有间割符的字符串或者HHMMSS格式的数值，假定是有意义的时间。例如：‘101112’被理解为‘10:11:12’，但‘109712’是不合法的(它有一个没有意义的分钟部分)，存储时将变为00:00:00。
【例5.6】创建数据表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。
【例5.7】表tmp4中插入值‘101112’，111213,‘0’，107010，SQL语句如下：
首先删除表中的数据：
DELETE FROM tmp4;
向表中插入数据：
mysql>INSERT INTO tmp4 values('101112'),(111213),( '0') ,( 107010);
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 1
可以看到在插入数据时，MySQL给出了一个警告信息，使用SHOW WARNINGS;查看警告信息：
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 1264 | Out of range value for column 't' at row 4 |
+---------+------+-----------------------------------------------------+
可以看到，在插入第4条记录的时候，数据超出了范围，原因是107010的小时部分超过了60，二小时是不会超过60的，查看结果：
mysql> SELECT * FROM tmp4;
+----------+
| t |
+----------+
| 10:11:12 |
| 11:12:13 |
| 00:00:00 |
| 00:00:00 |
+----------+
由结果可以看到，‘101112’被转换为10:11:12；111213被转换为11:12:13；‘0’被转换为00:00:00；107010因为是不合法的值，因此被转换为00:00:00。
也可以是用系统日期函数向TIME字段列插入值。
【例5.8】向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类型列，因为读者输入语句的时间不确定，因此获取的值与这里的可能是不同。但都是系统当前的日期时间值。
