mysql5.7的时间类型_【MySQL-5.7】日期与时间类型

MySQL中的日期与时间类型，主要包括：YEAR、TIME、DATE、DATETIME、TIMESTAMP，下表中列出了这几种类型的属性。

类型名称日期格式日期范围占用空间

YEARYYYY1901 ~ 21551字节

TIMEHH:MM:SS-838:59:59 ~ 838:59:593字节

DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33字节

DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598字节

TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07  UTC4字节

一、YEARYEAR类型可以使用三种格式表示：1. 以4位数字或字符串表示，两者效果相同，表示范围1901~2155，插入超出范围的数据会报错。2. 以2位字符串格式表示，范围为‘00’~‘99’。‘00’~‘69’表示2000~2069，‘70’~‘99’表示1970~1999。‘0’和‘00’都会被识别为2000，超出范围的数据也会被识别为2000。3. 以2位数字格式表示，范围为1~99。1~69表示2001~2069,70~99表示1970~1999。但0值会被识别为0000，这和2位字符串被识别为2000有所不同。下面是以上三种表示格式的例子：mysql> create table test1 (id year);

Query OK, 0 rows affected (0.04 sec)

mysql> desc test1;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | year(4) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

1 row in set (0.01 sec)(1)以4位字符串或数字格式表示mysql> insert into test1 values (2010),('2010');

Query OK, 2 rows affected (0.02 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test1;

+------+

| id |

+------+

| 2010 |

| 2010 |

+------+

2 rows in set (0.00 sec)

mysql> insert into test1 values (2156);

ERROR 1264 (22003): Out of range value for column 'id' at row 1(2)以2位字符串格式表示mysql> truncate table test1;

Query OK, 0 rows affected (0.04 sec)

mysql> insert into test1 values ('0'),('00'),('10'),('77');

Query OK, 4 rows affected (0.03 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test1;

+------+

| id |

+------+

| 2000 |

| 2000 |

| 2010 |

| 1977 |

+------+

4000

4 rows in set (0.00 sec)(3)以2位数字格式表示mysql> truncate table test1;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values (0),(00),(10),(77);

Query OK, 4 rows affected (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test1;

+------+

| id |

+------+

| 0000 |

| 0000 |

| 2010 |

| 1977 |

+------+

4 rows in set (0.00 sec)

二、TIMETIME类型可以用多种格式表示，如：

‘D HH:MM:SS':D表示天数，在插入表中时会折算成小时，即：D*24+HH:MM:SS。

'HHMMSS':省略冒号，但各个时间要有意义，比如‘128012’分钟部分会被识别为‘12：80：12’，80分钟是没有意义的，插入会报错。

'HH:MM':表示小时分钟，如‘12:08’表示12小时8分钟。

'SS':表示秒。

需要注意的是：如果输入‘1208’，虽然我们想让MySQL识别为12小时8分钟，但在这种格式下，MySQL识别为‘00:12:08’。

下面是几个例子：mysql> create table test2 (id time);

Query OK, 0 rows affected (0.32 sec)

mysql> desc test2;

+-------+------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------+------+-----+---------+-------+

| id | time | YES | | NULL | |

+-------+------+------+-----+---------+-------+

1 row in set (0.02 sec)mysql> insert into test2 values ('10:47:23'),('23:13'),('2 11:11'),('3 05'),('10');

Query OK, 5 rows affected (0.10 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from test2;

+----------+

| id |

+----------+

| 10:47:23 |

| 23:13:00 |

| 59:11:00 |

| 77:00:00 |

| 00:00:10 |

+----------+

5 rows in set (0.00 sec)

mysql> insert into test2 values ('3 5');

ERROR 1292 (22007): Incorrect time value: '3 5' for column 'id' at row 1mysql> truncate table test2;

Query OK, 0 rows affected (0.24 sec)

mysql> insert into test2 values ('105821'),(105821),('0');

Query OK, 3 rows affected (0.10 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into test2 values (108013);

ERROR 1292 (22007): Incorrect time value: '108013' for column 'id' at row 1

mysql> select * from test2;

+----------+

| id |

+----------+

| 10:58:21 |

| 10:58:21 |

| 00:00:00 |

+----------+

3 rows in set (0.00 sec)

三、DATEDATE类型可以通过如下格式表示：

‘YYYY-MM-DD','YYYYMMDD',YYYYMMDD,'YY-MM-DD','YYMMDD',YYMMDD

其中当使用两位表示年份时，分00~69和70~99两种，参考YEAR类型。

下面是几个DATE类型的例子：mysql> create table test3 (id date);

Query OK, 0 rows affected (0.25 sec)

mysql> desc test3;

+-------+------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------+------+-----+---------+-------+

| id | date | YES | | NULL | |

+-------+------+------+-----+---------+-------+

1 row in set (0.02 sec)mysql> insert into test3 values ('1992-03-08'),('19920308'),(19920308);

Query OK, 3 rows affected (0.11 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test3;

+------------+

| id |

+------------+

| 1992-03-08 |

| 1992-03-08 |

| 1992-03-08 |

+------------+

3 rows in set (0.00 sec)mysql> truncate table test3;

Query OK, 0 rows affected (0.23 sec)

mysql> insert into test3 values ('92-03-08'),('920308'),(920308),('10-12-20');

Query OK, 4 rows affected (0.09 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test3;

+------------+

| id |

+------------+

| 1992-03-08 |

| 1992-03-08 |

| 1992-03-08 |

| 2010-12-20 |

+------------+

4 rows in set (0.00 sec)

四、DATETIMEDATETIME类型，包含日期和时间部分。可以使用引号字符串或数字两种，年份可以是4位，也可以是2位，在此不再赘述，请参照上面的DATE和TIME类型。下面是几个例子：mysql> create table test4 (id datetime);

Query OK, 0 rows affected (0.30 sec)

mysql> desc test4;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| id | datetime | YES | | NULL | |

+-------+----------+------+-----+---------+-------+

1 row in set (0.01 sec)mysql> insert into test4 values ('1992-03-08 11:11:11'),(19920308111111),('19920308111111'),(20101231080808);

Query OK, 4 rows affected (0.10 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from test4;

+---------------------+

| id |

+---------------------+

| 1992-03-08 11:11:11 |

| 1992-03-08 11:11:11 |

| 1992-03-08 11:11:11 |

| 2010-12-31 08:08:08 |

+---------------------+

4 rows in set (0.00 sec)mysql> truncate table test4;

Query OK, 0 rows affected (0.19 sec)

mysql> insert into test4 values (920308111111),('92-03-08 11:11:11');

Query OK, 2 rows affected (0.11 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test4;

+---------------------+

| id |

+---------------------+

| 1992-03-08 11:11:11 |

| 1992-03-08 11:11:11 |

+---------------------+

2 rows in set (0.00 sec)

五、TIMESTAMPTIMESTAMP类型和DATETIME类型的表示格式相同，存储4个字节(比DATETIME少)，取值范围少于DATETIME类型。TIMESTAMP和DATETIME最大不同于：TIMESTAMP根据时区显示时间。如果不明白可以看下面的例子：mysql> create table test5 (id timestamp);

Query OK, 0 rows affected (0.27 sec)

mysql> desc test5;

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| id | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------+-----------+------+-----+-------------------+-----------------------------+

1 row in set (0.01 sec)

mysql> insert into test5 values (NOW());

Query OK, 1 row affected (0.06 sec)

mysql> select * from test5;

+---------------------+

| id |

+---------------------+

| 2018-03-28 13:16:09 |

+---------------------+

1 row in set (0.00 sec)

#设置为东十区，比东八区快两个小时。

mysql> set time_zone='+10:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test5;

+---------------------+

| id |

+---------------------+

| 2018-03-28 15:16:09 |

+---------------------+

1 row in set (0.00 sec)总结：日期与时间类型上面说的差不多了，需要补充一点：可以使用current_date(当前日期)、current_time(当前时间)、now()(当前日期和时间，根据字段类型显示日期或者时间)，向表中插入当前的日期或者时间点。