参考资料
    练习题 icon lost
    交流讨论
    我的笔记
    专栏课程
    资料来源 9.2 日期和时间类型
    视频作者 高雯婧

    9.2 日期和时间类型

    MySQL提供了表示日期和时间的数据类型,主要有YEAR类型、TIME类型、DATE类型、DATETIME类型和TIMESTAMP类型。

    ·DATE类型通常用来表示年月日;

    ·DATETIME类型通常用来表示年、月、日、时、分、秒;

    ·TIME类型通常用来表示时、分、秒。

    日期和时间类型占用的存储空间如表9-5所示。

    表9-5 日期和时间类型占用的存储空间

    由表9-5可以看出,在日期和时间类型中,占用存储空间最小的是YEAR类型,总共占用1个字节的存储空间;占用存储空间最大的是DATETIME类型,总共占用8个字节的存储空间。

    日期和时间类型表示的范围如表9-6所示。

    表9-6 日期和时间类型表示的范围

    每种日期和时间类型都有一个有效值范围,如果超出这个有效值范围,则会以0进行存储,每种日期和时间类型的零值表示如表9-7所示。

    表9-7 日期和时间类型的零值表示

    日期和时间类型的零值格式符合每种日期和时间类型定义的格式。


    9.2.1 YEAR类型

    YEAR类型用来表示年份,在所有的日期时间类型中所占用的存储空间最小,只需要1个字节的存储空间。

    在MySQL中,YEAR有以下几种存储格式:

    ·以4位字符串或数字格式表示YEAR类型,其格式为YYYY,最小值为1901,最大值为2155。

    ·以2位字符串格式表示YEAR类型,最小值为00,最大值为99。其中,当取值为00到69时,表示2000到2069;当取值为70到99时,表示1970到1999。如果插入的数据超出了取值范围,则MySQL会将值自动转换为2000。

    ·以2位数字格式表示YEAR类型,最小值为1,最大值为99。其中,当取值为1到69时,表示2001到2069,当取值为70到99时,表示1970到1999。

    注意:当使用两位数字格式表示YEAR类型时,数值0将被转化为0000。

    创建数据表t9,包含一个YEAR类型的字段y。


    mysql> CREATE TABLE t9 (
        -> y YEAR
        -> );
    Query OK, 0 rows affected (0.12 sec)
    

    向t9表中插入两条数据,分别为数字2020和字符串2020。


    mysql> INSERT INTO t9 (y) VALUES (2020), ('2020'); 
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    查看t9表中的数据。


    mysql> SELECT * FROM t9;
    +------+
    | y    |
    +------+
    | 2020 |
    | 2020 |
    +------+
    2 rows in set (0.00 sec)
    

    此时,当向数据表中插入4位数字或字符串表示的YEAR类型时,都能够正确地插入数据表中。

    向数据表中插入字符串表示的年份2156。


    mysql> INSERT INTO t9 (y) VALUES ('2156');
    ERROR 1264 (22003): Out of range value for column 'y' at row 1
    

    此时,MySQL抛出超出范围的错误,数据不能被正确地插入数据表中。

    接下来清空数据表t9中的数据。


    mysql> DELETE FROM t9;
    Query OK, 2 rows affected (0.00 sec)
    

    再次向数据表中插入两位字符串表示的YEAR类型的数据,分别为0、00、88、20。


    mysql> INSERT INTO t9 (y) VALUES ('0'),('00'), ('88'), ('20');   
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    

    数据插入成功,查看t9表中的数据。


    mysql> SELECT * FROM t9;
    +------+
    | y    |
    +------+
    | 2000 |
    | 2000 |
    | 1988 |
    | 2020 |
    +------+
    4 rows in set (0.00 sec)
    

    0和00被转化为了2000,88被转化为了1988,20被转化为了2020。

    接下来,再次清空数据表t9中的数据。


    mysql> DELETE FROM t9;
    Query OK, 4 rows affected (0.00 sec)
    

    向t9表中插入两位数字表示的YEAR类型的数据,分别为0、00、88、20。


    mysql> INSERT INTO t9 (y) VALUES (0), (00), (88), (20);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    

    查看t9表中的数据。


    mysql> SELECT * FROM t9;
    +------+
    | y    |
    +------+
    | 0000 |
    | 0000 |
    | 1988 |
    | 2020 |
    +------+
    4 rows in set (0.00 sec)
    

    数字0和00被转化为了0000,88被转化为了1988,20被转化为了2020。

    再次清空数据表t9中的数据,向数据表中插入数字100。


    mysql> DELETE FROM t9;
    Query OK, 4 rows affected (0.00 sec)
    mysql> INSERT INTO t9 (y) VALUES (100);
    ERROR 1264 (22003): Out of range value for column 'y' at row 1
    

    可以看到,由于100已经超出了0到99的范围,MySQL抛出超出范围的错误,数据不能被正确地插入数据表中。


    9.2.2 TIME类型

    TIME类型用来表示时间,不包含日期部分。在MySQL中,需要3个字节的存储空间来存储TIME类型的数据,可以使用“HH:MM:SS”格式来表示TIME类型,其中,HH表示小时,MM表示分钟,SS表示秒。

    在MySQL中,向TIME类型的字段插入数据时,也可以使用几种不同的格式。

    (1)可以使用带有冒号的字符串,比如D HH:MM:SS、HH:MM:SS、HH:MM、D HH:MM、D HH或SS格式,都能被正确地插入TIME类型的字段中。其中D表示天,其最小值为0,最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时,D会被转化为小时,计算格式为D*24+HH。

    (2)可以使用不带有冒号的字符串或者数字,格式为"HHMMSS"或者HHMMSS。如果插入一个不合法的字符串或者数字,MySQL在存储数据时,会将其自动转化为00:00:00进行存储。

    (3)使用CURRENT_TIME或者NOW(),会插入当前系统的时间。

    注意:当使用带有冒号并且不带D的字符串表示时间时,表示当天的时间,比如12:10表示12:10:00,而不是00:12:10;当使用不带冒号的字符串或数字表示时间时,MySQL会将最右边的两位解析成秒,比如1210,表示00:12:10,而不是12:10:00。

    创建数据表t10,t10表中包含一个TIME类型的字段t。


    mysql> CREATE TABLE t10 (
        -> t TIME
        -> );
    Query OK, 0 rows affected (0.02 sec)
    

    向t10表中插入数据,分别为2 12:30:29、12:35:29、12:40、2 12:40、45。


    mysql> INSERT INTO t10 (t) VALUES ('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'), ('45');      
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    

    数据成功插入数据表,接下来查看t10表中的数据。


    mysql> SELECT * FROM t10;
    +----------+
    | t        |
    +----------+
    | 60:30:29 |
    | 12:35:29 |
    | 12:40:00 |
    | 60:40:00 |
    | 00:00:45 |
    +----------+
    5 rows in set (0.00 sec)
    

    MySQL在存储数据时,将2 12:30:29转化为了60:30:29,将12:40转化为了12:40:00,将2 12:40转化为了60:40:00,将45转化为了00:00:45。

    清空t10表的数据。


    mysql> DELETE FROM t10;
    Query OK, 5 rows affected (0.00 sec)
    

    向t10表中插入数据1:1:1。


    mysql> INSERT INTO t10 (t) VALUES ('1:1:1');
    Query OK, 1 row affected (0.00 sec)
    

    查看t10表中的数据。


    mysql> SELECT * FROM t10;
    +----------+
    | t        |
    +----------+
    | 01:01:01 |
    +----------+
    1 row in set (0.00 sec)
    

    MySQL会自动补齐0。

    再次清空表t10中的数据,并插入123520、124011和0。


    mysql> DELETE FROM t10;
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT INTO t10 (t) VALUES ('123520'), (124011), ('0');
    Query OK, 3 rows affected (0.01 sec)
    Records: 3  Duplicates: 0  Warnings: 0
    

    数据插入成功,查看t10表中的数据。


    mysql> SELECT * FROM t10;
    +----------+
    | t        |
    +----------+
    | 12:35:20 |
    | 12:40:11 |
    | 00:00:00 |
    +----------+
    3 rows in set (0.00 sec)
    

    123520被转化为了12:35:20,124011被转化为了12:40:11,0被转化为了00:00:00。

    再次向表中插入126110。


    mysql> INSERT INTO t10 (t) VALUES (126110);
    ERROR 1292 (22007): Incorrect time value: '126110' for column 't' at row 1
    

    MySQL抛出了不正确的时间数值的错误。

    再次清空t10表中的数据,使用MySQL自带的时间函数向t10表中插入数据。


    mysql> INSERT INTO t10 (t) VALUES (NOW()), (CURRENT_TIME);
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    在MySQL中,函数NOW()和CURRENT_TIME都能够获取系统的当前时间,通过时间函数向t10表中插入数据成功。

    查询t10表中的数据。


    mysql> SELECT * FROM t10;
    +----------+
    | t        |
    +----------+
    | 22:28:03 |
    | 22:28:03 |
    +----------+
    2 rows in set (0.00 sec)
    

    使用时间函数能够向TIME类型的字段值正确地插入系统的当前时间。


    9.2.3 DATE类型

    DATE类型表示日期,没有时间部分,格式为YYYY-MM-DD,其中,YYYY表示年份,MM表示月份,DD表示日期。需要3个字节的存储空间。在向DATE类型的字段插入数据时,同样需要满足一定的格式条件。

    ·以YYYY-MM-DD格式或者YYYYMMDD格式表示的字符串日期,其最小取值为1000-01-01,最大取值为9999-12-03。

    ·以YY-MM-DD格式或者YYMMDD格式表示的字符串日期,此格式中,年份为两位数值或字符串满足YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。

    ·以YYYYMMDD格式表示的数字日期,能够被转化为YYYY-MM-DD格式。

    ·以YYMMDD格式表示的数字日期,同样满足年份为两位数值或字符串YEAR类型的格式条件为:当年份取值为00到69时,会被转化为2000到2069;当年份取值为70到99时,会被转化为1970到1999。

    ·使用CURRENT_DATE或者NOW()函数,会插入当前系统的日期。

    创建数据表t11,t11表中只包含一个DATE类型的字段d。


    mysql> CREATE TABLE t11 (
        -> d DATE
        -> );
    Query OK, 0 rows affected (0.13 sec)
    

    接下来向t11表中插入YYYY-MM-DD格式和YYYYMMDD格式的字符串日期2020-10-01和20201001。


    mysql> INSERT INTO t11 (d) VALUES ('2020-10-01'), ('20201001');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    可以看到,符合DATE格式要求的字符串日期能够正确地插入数据表中。

    查看t11表中的数据。


    mysql> SELECT * FROM t11;
    +------------+
    | d          |
    +------------+
    | 2020-10-01 |
    | 2020-10-01 |
    +------------+
    2 rows in set (0.00 sec)
    

    清空t11表中的数据,并向t11表中插入符合YYYYMMDD格式的数字日期20201001。


    mysql> DELETE FROM t11;
    Query OK, 2 rows affected (0.00 sec)
    mysql> INSERT INTO t11 (d) VALUES  (20201001);             
    Query OK, 1 row affected (0.00 sec)
    

    向数据表中插入数据成功。接下来查看数据表t11中的数据。


    mysql> SELECT * FROM t11;
    +------------+
    | d          |
    +------------+
    | 2020-10-01 |
    +------------+
    1 row in set (0.00 sec)
    

    数字格式的日期20201001被正确转化为2020-10-01。

    删除t11表中的数据,并向t11表中插入YYYY-MM-DD和YYYYMMDD字符串日期格式的数据00-01-01、000101、69-10-01、691001、70-01-01、700101、99-01-01和990101。


    mysql> DELETE FROM t11;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> INSERT INTO t11 (d) VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), ('700101'), ('99-01-01'), ('990101');
    Query OK, 8 rows affected (0.00 sec)
    Records: 8  Duplicates: 0  Warnings: 0
    

    数据被正确插入数据表中,接下来查看数据表t11中的数据。


    mysql> SELECT * FROM t11;
    +------------+
    | d          |
    +------------+
    | 2000-01-01 |
    | 2000-01-01 |
    | 2069-10-01 |
    | 2069-10-01 |
    | 1970-01-01 |
    | 1970-01-01 |
    | 1999-01-01 |
    | 1999-01-01 |
    +------------+
    8 rows in set (0.00 sec)
    

    00-01-01和000101被转化为了2000-01-01,69-10-01和691001被转化为了2069-10-01,70-01-01和700101被转化为了1970-01-01,99-01-01和990101被转化为了1999-01-01。

    再次删除t11表中的数据,并向t11表中插入YYYYMMDD格式的数字日期000101、691001、700101和990101。


    mysql> DELETE FROM t11;
    Query OK, 8 rows affected (0.00 sec)
    mysql> INSERT INTO t11 (d) VALUES (000101), (691001), (700101), (990101);         
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    

    查看t11表中的数据。


    mysql> SELECT * FROM t11;
    +------------+
    | d          |
    +------------+
    | 2000-01-01 |
    | 2069-10-01 |
    | 1970-01-01 |
    | 1999-01-01 |
    +------------+
    4 rows in set (0.00 sec)
    

    可以看到,向数据表t11中正确插入了数据。

    接下来再次删除t11表中的数据,使用函数CURRENT_DATE()和NOW()向数据表中插入数据。


    mysql> DELETE FROM t11;
    Query OK, 4 rows affected (0.00 sec)
    mysql> INSERT INTO t11 (d) VALUES (CURRENT_DATE()), (NOW());
    Query OK, 2 rows affected, 1 warning (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 1
    

    查看t11表中的数据。


    mysql> SELECT * FROm t11;
    +------------+
    | d          |
    +------------+
    | 2019-12-06 |
    | 2019-12-06 |
    +------------+
    2 rows in set (0.00 sec)
    

    使用函数CURRENT_DATE()和NOW()成功向数据表t11中正确插入了系统的当前日期。


    9.2.4 DATETIME类型

    DATETIME类型在所有的日期时间类型中占用的存储空间最大,总共需要8个字节的存储空间。在格式上为DATE类型和TIME类型的组合,可以表示为YYYY-MM-DD HH:MM:SS,其中YYYY表示年份,MM表示月份,DD表示日期,HH表示小时,MM表示分钟,SS表示秒。

    在向DATETIME类型的字段插入数据时,同样需要满足一定的格式条件。

    ·以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时,最小值为10000-01-01 00:00:00,最大值为9999-12-03 23:59:59。

    ·以YY-MM-DD HH:MM:SS格式或者YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。

    ·以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,会被转化为YYYY-MM-DD HH:MM:SS格式。

    ·以YYMMDDHHMMSS格式的数字插入DATETIME类型的字段时,两位数的年份规则符合YEAR类型的规则,00到69表示2000到2069;70到99表示1970到1999。

    ·使用函数CURRENT_TIMESTAMP()和NOW(),可以向DATETIME类型的字段插入系统的当前日期和时间。

    创建数据表t12,数据表t12中包含一个DATETIME类型的字段dt。


    mysql> CREATE TABLE t12 (
        -> dt DATETIME
        -> );
    Query OK, 0 rows affected (0.02 sec)
    

    向数据表t12中插入满足YYYY-MM-DD HH:MM:SS格式和YYYYMMDDHHMMSS格式的字符串时间2020-01-01 00:00:00和20200101000000。


    mysql> INSERT INTO t12 (dt) VALUES ('2020-01-01 00:00:00'), ('20200101000000');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    向数据表中插入数据成功。接下来查看t12数据表中的数据。


    mysql> SELECT * FROM t12;
    +---------------------+
    | dt                   |
    +---------------------+
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)
    

    可以看到,数据表t12中已经正确插入了数据。

    接下来,清空数据表t12中的数据,向t12表中插入满足YY-MM-DD HH:MM:SS格式和YYMMDDHHMMSS格式的字符串时间99-01-01 00:00:00、990101000000、20-01-01 00:00:00和200101000000。


    mysql> DELETE FROM t12;
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> INSERT INTO t12 (dt) VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    

    查看t12表中的数据。


    mysql> SELECT * FROM t12;
    +---------------------+
    | dt                  |
    +---------------------+
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    +---------------------+
    4 rows in set (0.00 sec)
    

    99-01-01 00:00:00和990101000000被转化为了1999-01-01 00:00:00;20-01-01 00:00:00和200101000000被转化为了2020-01-01 00:00:00。

    清空t12表中的数据,并向t12表中插入满足YYYYMMDDHHMMSS格式和YYMMDD-HHMMSS格式的数字时间20200101000000、200101000000、19990101000000、990101000000。


    mysql> DELETE FROM t12;
    Query OK, 4 rows affected (0.00 sec)
    
    mysql> INSERT INTO t12 (dt) VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);
    Query OK, 4 rows affected (0.00 sec)
    Records: 4  Duplicates: 0  Warnings: 0
    

    向数据表t12中插入数据成功。接下来查看数据表t12中的数据。


    mysql> SELECT * FROM t12;
    +---------------------+
    | dt                  |
    +---------------------+
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    +---------------------+
    4 rows in set (0.00 sec)
    

    20200101000000和200101000000被转化为了2020-01-01 00:00:00;19990101000000和990101000000被转化为了1999-01-01 00:00:00。

    再次清空t12表中的数据,使用函数CURRENT_TIMESTAMP()和NOW()向数据表t12中插入数据。


    mysql> INSERT INTO t12 (dt) VALUES (CURRENT_TIMESTAMP()), (NOW());
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    已经向t12表中成功插入了数据。接下来查看数据表t12中的数据。


    mysql> SELECT * FROM t12;
    +---------------------+
    | dt                  |
    +---------------------+
    | 2019-12-06 11:59:12 |
    | 2019-12-06 11:59:12 |
    +---------------------+
    2 rows in set (0.00 sec)
    

    可以看到,使用函数CURRENT_TIMESTAMP()和NOW()向t12表中正确插入了系统的当前时间。


    9.2.5 TIMESTAMP类型

    TIMESTAMP类型也可以表示日期时间,其显示格式与DATETIME类型相同,都是YYYY-MM-DD HH:MM:SS,需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多,只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中,UTC表示世界统一时间,也叫作世界标准时间。

    如果向TIMESTAMP类型的字段插入的时间超出了TIMESTAMP类型的范围,则MySQL会抛出错误信息。

    创建数据表t13,t13表中包含一个TIMESTAMP类型的字段ts。


    mysql> CREATE TABLE t13 (
        -> ts TIMESTAMP
        -> );
    Query OK, 0 rows affected (0.01 sec)
    

    向t13数据表中插入数据1999-01-01 00:00:00、19990101000000、99-01-01 00:00:00、990101000000、20-01-01 00:00:00和200101000000。


    mysql> INSERT INTO t13 (ts) VALUES ('1999-01-01 00:00:00'), ('19990101000000'), ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'), ('200101000000');
    Query OK, 6 rows affected (0.00 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    

    查看t13表中的数据。


    mysql> SELECT * FROM t13;
    +---------------------+
    | ts                  |
    +---------------------+
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    +---------------------+
    6 rows in set (0.00 sec)
    

    向TIMESTAMP类型的字段插入数据时,当插入的数据格式满足YY-MM-DD HH:MM:SS和YYMMDDHHMMSS时,两位数值的年份同样符合YEAR类型的规则条件,只不过表示的时间范围要小很多。

    清空t13表中的数据,并向表中插入以@符号分隔的字符串日期2020@01@01@00@00 @00,和20@01@01@00@00@00。


    mysql> DELETE FROM t13;
    Query OK, 6 rows affected (0.00 sec)
    
    mysql> INSERT INTO t13 VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    数据插入成功。接下来,查看数据表t13中的数据。


    mysql> SELECT * FROM t13;
    +---------------------+
    | ts                  |
    +---------------------+
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    +---------------------+
    2 rows in set (0.00 sec)
    

    MySQL将以@符号分隔的字符串日期2020@01@01@00@00@00和20@01@01@00@00 @00,正确地转化为了2020-01-01 00:00:00。

    清空t13表数据,使用函数CURRENT_TIMESTAMP()和NOW()向t13表中插入数据。


    mysql> DELETE FROM t13;
    Query OK, 2 rows affected (0.00 sec)
    mysql> INSERT INTO t13 (ts) VALUES (CURRENT_TIMESTAMP()), (NOW());
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    

    查看t13表中的数据。


    mysql> SELECT * FROM t13;
    +---------------------+
    | ts                  |
    +---------------------+
    | 2019-12-06 16:45:14 |
    | 2019-12-06 16:45:14 |
    +---------------------+
    2 rows in set (0.00 sec)
    

    由此可以证明,使用函数CURRENT_TIMESTAMP()和NOW()可以向TIMESTAMP类型的字段插入系统的当前时间。

    实际上,TIMESTAMP在存储数据的时候是以UTC(世界统一时间,也叫作世界标准时间)格式进行存储的,存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。

    创建数据表t14,t14表中包含两个TIMESTAMP类型的字段ts1和ts2,其中,ts1字段不为空并且默认为CURRENT_TIMESTAMP。


    mysql> CREATE TABLE t14 (
        -> ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        -> ts2 TIMESTAMP
        -> );
    Query OK, 0 rows affected (0.02 sec)
    

    查看当前所在的时区。


    mysql> SHOW VARIABLES LIKE 'time_zone'; 
    +---------------+--------+
    | Variable_name | Value  |
    +---------------+--------+
    | time_zone     | SYSTEM |
    +---------------+--------+
    1 row in set (0.00 sec)
    

    time_zone时区的值为SYSTEM,也就是服务器所在的东八区。

    使用NOW()函数插入系统的当前时间。


    mysql> INSERT INTO t14 (ts2) VALUES (NOW());
    Query OK, 1 row affected (0.00 sec)
    

    查看t14表中的数据。


    mysql> SELECT * FROM t14;
    +---------------------+---------------------+
    | ts1                 | ts2                 |
    +---------------------+---------------------+
    | 2019-12-06 17:20:01 | 2019-12-06 17:20:01 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    

    ts1字段和ts2字段插入的数据相同。

    修改当前系统的时区,将时区修改为东6区。


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

    设置成功,再次查看当前系统所在的时区。


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

    当前系统的时区已经被修改为东6区了。

    再次查看t14表中的数据。


    mysql> SELECT * FROM t14;
    +---------------------+---------------------+
    | ts1                 | ts2                 |
    +---------------------+---------------------+
    | 2019-12-06 15:20:01 | 2019-12-06 15:20:01 |
    +---------------------+---------------------+
    1 row in set (0.00 sec)
    

    东8区的时间会比东6区的时间快2个小时,同时,时区不同,从TIMESTAMP字段中查询出的时间也不相同。

    清空t14表的数据,向t14表中的ts2字段插入一个超出范围的时间值。


    mysql> INSERT INTO t14 (ts2) VALUES ('2038-01-20 00:00:00');
    ERROR 1292 (22007): Incorrect datetime value: '2038-01-20 00:00:00' for column 'ts2' at row 1
    

    当向TIMESTAMP字段插入超出范围的时间时,MySQL会抛出错误,不能插入数据。

    资料来源 9.2 日期和时间类型
    博客作者 weixin_41275260
    前往答题
    发布见解