MySQL-日期数据类型

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期 时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期 时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

YEAR 类型

• 以4位字符串或数字格式表示YEAR类型，其格式为YYYY，最小值为1901，最大值为2155
• 以2位字符串格式表示YEAR类型，最小值为00，最大值为99
  • 当取值为01到69时，表示2001到2069
  • 当取值为70到99时，表示1970到1999
  • 当取值整数的0或00添加的话，那么是0000年
  • 当取值是日期/字符串的'0'添加的话，是2000年
• 从MySQL 5.5.27开始，2位格式的YEAR已经不推荐使用。YEAR默认格式就是“YYYY”
• 从MySQL 8.0.19开始，不推荐使用指定显示宽度的YEAR(4)数据类型

CREATE TABLE test_year(
    f1 YEAR,
    f2 YEAR(4)
);

INSERT INTO test_year VALUES('2020','2021');

INSERT INTO test_year VALUES('45','71');

INSERT INTO test_year VALUES(0,'0');

DATE 类型

• 以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
• 以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
• 使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期

CREATE TABLE test_date1(
    f1 DATE
);

INSERT INTO test_date1 VALUES ('2020-10-01'), ('20201001'),(20201001);

INSERT INTO test_date1
VALUES ('00-01-01'), ('000101'), ('69-10-01'), ('691001'), ('70-01-01'), 
('700101'), ('99-01-01'), ('990101');

INSERT INTO test_date1
VALUES (000301), (690301), (700301), (990301);

INSERT INTO test_date1
VALUES (CURRENT_DATE()), (NOW());

TIME 类型

• 使用带有冒号的字符串，比如' 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
  • 当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10
• 使用不带有冒号的字符串或者数字，格式为' HHMMSS '或者 HHMMSS
  • 如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储
  • 比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00
• 使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间

CREATE TABLE test_time1(
    f1 TIME
);

INSERT INTO test_time1
VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');

INSERT INTO test_time1 VALUES ('123520'), (124011),(1210);

INSERT INTO test_time1 VALUES (NOW()), (CURRENT_TIME());

DATETIME 类型

• 以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-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
• 使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间

CREATE TABLE test_datetime1(
    dt DATETIME
);

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'), ('20210101065030');

INSERT INTO test_datetime1
VALUES ('99-01-01 00:00:00'), ('990101000000'), ('20-01-01 00:00:00'),
('200101000000');

INSERT INTO test_datetime1
VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);

INSERT INTO test_datetime1 VALUES (CURRENT_TIMESTAMP()), (NOW());

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

CREATE TABLE test_timestamp1(
    ts TIMESTAMP
);

INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'), ('19990101030405'), ('99-01-01 03:04:05'),
('990101030405');

INSERT INTO test_timestamp1
VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');

INSERT INTO test_timestamp1 VALUES (CURRENT_TIMESTAMP()), (NOW());

#Incorrect datetime value
INSERT INTO test_timestamp1 VALUES ('2038-01-20 03:14:07');

TIMESTAMP和DATETIME的区别？

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的

CREATE TABLE temp_time(
    d1 DATETIME,
    d2 TIMESTAMP
);

INSERT INTO temp_time VALUES('2021-9-2 14:45:52','2021-9-2 14:45:52');
INSERT INTO temp_time VALUES(NOW(),NOW());

#修改当前的时区
SET time_zone = '+9:00';