MYSQL日期类型总结

1、日期总结

各种各样的日期格式让人头疼，对于mysql的全部日期进行了总结，mysql中有以下几种表示时间的字段类型

日期/时间类型	日期格式	日期最小值	日期最大值	存储需求
YEAR	YYYY	1901	2155	1字节
TIMF	HH:MM:S	-838:5959	838:59:59	3字节
DATE	SYYYY-MMDD	1000-01-01	9999-12-03	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:01UTC	2038-01-19 03:14:07 UTC	4字节

2、YEAR

YEAR见名知意，只是表达年份的字段。最小为1901，最大为2155【0000年表示错误时】

接下来按步骤进行演示：

1、首先创建表t1

CREATE TABLE `t1`(
	y YEAR
);

2、在表中插入两条数据分别为数字2023和字符串2023。

INSERT INTO `t1` VALUES (2023),('2023');

3、查询表中的数据

SELECT * FROM t1;

4、添加2156年，进行尝试

INSERT into 't1' VALUES (2156);

发现会报错：

INSERT into 't1' VALUES(2156)
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''t1' VALUES(2156)' at line 1
> 查询时间: 0s

5、删除全部数据

DELETE FROM t1

6、添加特殊的数据

INSERT INTO t1 VALUES ('0'),('00'),('88'),('20');

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

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

DELETE FROM t1;
INSERT INTO t1 VALUES (100);

结果发现100无法插入进去。

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

7、插入一个、两个、三个0都是2000年，但是插入四个0的时候出现了0000，超出了范围但是没报错。【0000年表示错误时】

3、TIME

TIME类型表示时间，不包含时间信息，包含的只是基本的时分秒。HH是时，MM是分，SS是秒，D表示天数，最终在数据库存储时会表示为：HH（最终） = d * 24 + HH（原先）

（1）可以使用带有冒号的字符串

（2）可以使用不带有冒号的字符串或者数字，最终在数据库中变为规范的格式

（3）使用CURRENT_TIME或者NOW()函数，会插入当前系统的时间。

接下来按步骤进行演示：

1、创建数据库

CREATE TABLE t2(
	t TIME
)

2、插入数据

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

3、清空表

DELETE FROM t2

4、插入特殊数据

INSERT INTO t2 VALUES('1:1:1');

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

INSERT INTO t2 VALUES ('123520'), (124011), ('0');

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

6、向表中插入126110

INSERT INTO t2 VALUES (126110);

报错信息：

7、插入当前时间

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

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

4、DATE

DATE类型表示日期，不包含每天的时间部分。格式为YYYY-MM-DD，其中YYYY表示年份，MM表示月份，DD表示天。

年份为两位数值或字符串满足YEAR类型的格式条件

接下来按步骤进行演示：

1、创建表

CREATE TABLE t3(
	d DATE
);

2、插入数据

INSERT INTO t11 (d) VALUES ('2023-7-20'), ('20230720');

注意：如果时间中间不加 **- **进行分割，只能写07而不能写7，带 - 分割的无影响

3、插入非字符串日期

DELETE FROM t3;
INSERT INTO t3 VALUES(20230720);

4、插入年份特殊数据

DELETE FROM t3;
INSERT INTO t3 ( d )
VALUES
	( '00-01-01' ),
	( '000101' ),
	( '69-10-01' ),
	( '691001' ),
	( '70-01-01' ),
	( '700101' ),
	( '99-01-01' ),
	( '990101' );

可以看出来年份跟YEAR类型是一样的，并且无论带不带 - 都可以转为规范的格式

5、插入非字符串数据

DELETE FROM t3;
INSERT INTO t3 VALUES (000101), (691001), (700101), (990101);

6、插入当前日期函数数据

DELETE FROM t3;
INSERT INTO t3 VALUES (CURRENT_DATE()), (NOW());

5、DATETIME

显而易见DATETIME是DATE+TIME，整体格式为YYYY-MM-DD HH:MM:SS。1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。

并且年份依旧符合YEAR规则，00到69表示2000到2069，70到99表示1970到1999。

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

1、创建数据表

CREATE TABLE t4(
	dt DATETIME
);

2、插入字符串2020-01-01 00:00:00和20200101000000

INSERT INTO t4 VALUES ('2020-01-01 00:00:00'), ('20200101000000');

3、插入YY-MM-SS HH:MM:SS格式的数据

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

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

4、插入非字符串类型数据

DELETE FROM t4;
INSERT INTO t4 VALUES (20200101000000), (200101000000), (19990101000000), (990101000000);

20200101000000被转为2020-01-01 00:00:00，其他的类似

5、插入当前时间的函数

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

6、TIMESTAMP

这个类型数据也可以表示时间，但是占的字节比DATETIME小，因为TIMESTAMP的范围比DATETIME小，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间，并且插入的数据不能超过这个时间。

1、创建表

CREATE TABLE t5(
	ts 	TIMESTAMP
);

2、向表中插入字符串类型数据

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

3、向表中插入以@符号分隔的字符串日期

DELETE FROM t5;
INSERT INTO t5 VALUES ('2020@01@01@00@00@00'), ('20@01@01@00@00@00');

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

4、使用当前日期函数插入数据信息

DELETE FROM t5;
INSERT INTO t5 VALUES (CURRENT_TIMESTAMP()), (NOW());

5、时区问题总结

创建对应的表

CREATE TABLE t6 (
    ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ts2 TIMESTAMP
);

查看当前所在的时区

SHOW VARIABLES LIKE 'time_zone'; 

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

INSERT INTO t14 (ts2) VALUES (NOW());

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

SET time_zone = '+6:00'

然后再查询数据库中的时间，会发现跟电脑上的日期不同

SELECT * FROM t6;

清空表中的数据，并插入一条超出范围的数据

INSERT INTO t14 (ts2) VALUES ('2038-01-20 00:00:00');

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

许久未更新，已成功入职，以后会经常更新小东西，一起进步。