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会抛出错误,不能插入数据。
许久未更新,已成功入职,以后会经常更新小东西,一起进步。