P62 日期与时间类型

6.日期与时间类型

1.year类型

#6.1 year类型

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

desc test_year;

insert into test_year(f1)
values('2021'),(2022);

select * from test_year;

INSERT INTO test_year(f1)
VALUES('2155');
#报错，超出范围Out of range value for column 'f1' at row 1
INSERT INTO test_year(f1)
VALUES('2156');

#不推荐使用两位的写法
INSERT INTO test_year(f1)
VALUES('69'),('70');

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

2.DATE类型

#6.2 DATE类型
CREATE TABLE test_date1(
f1 DATE
);

DESC test_date1;
#带短横线的是标准写法
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()), (CURRENT_DATE()),(NOW());

SELECT * FROM test_date1;

3.TIME类型

CREATE TABLE test_time1(
f1 TIME
);

DESC test_time1;

#前面的2代表的是day，也就是2天:48:00:00+12:30:29
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()),(CURTIME());

SELECT * FROM test_time1;

4.DATETIME类型

#6.4 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());

SELECT * FROM test_datetime1;

5.TIMESTAMP类型

#6.5 TIMESTAMP类型

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，上限为2038-01-19 03:14:07
INSERT INTO test_timestamp1
VALUES ('2038-01-20 03:14:07');

SELECT * FROM test_timestamp1;

#对比DATETIME 和 TIMESTAMP
#TIMESTAMP：需要考虑时区问题
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());

SELECT * FROM temp_time;

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

SELECT * FROM temp_time;

6.开发中经验