一、mysql四种时间类型
首先创建一个表用于测试,举例:
create table table_name
(
id int auto_increment
primary key,
dateTime1 datetime null,
date1 date null,
timeStamp1 timestamp null,
time1 time null
);
insert into table_name(dataTime1, timeStamp1, date1, time1)values ('2021-12-27 17:21:31', null, null, null);
insert into table_name(dataTime1, timeStamp1, date1, time1)values (null, '2021-12-27 17:21:31', null, null);
insert into table_name(dataTime1, timeStamp1, date1, time1)values (null, null, '2021-12-27', null);
insert into table_name(dataTime1, timeStamp1, date1, time1)values (null, null, null, '17:21:31');
说明:创建了一个table_name的表,和时间类型有关的四个参数,紧接着往表里插入四条数据。不难发现其实每种类型数据库都允许null值出现。
1. datetime
select * from table_name where dataTime1 > '2021-12-26 17:21:31';
或
select * from table_name where dataTime1 > timestamp('2021-12-26 17:21:30');
查询结果都是:
2. date
select * from table_name where date1 > date ('2021-12-26 17:21:30');
或
select * from table_name where date1 > '2021-12-26 17:21:30';
或
select * from table_name where date1 > '2021-12-26';
查询结果都是:
3. timestamp
select * from table_name where timeStamp1 > timestamp ('2021-12-26 17:21:30');
或
select * from table_name where timeStamp1 > '2021-12-26 17:21:30';
查询结果都是:
4. time
select * from table_name where time1 > time('2021-12-26 17:21:30');
或
select * from table_name where time1 > '17:21:30';
查询结果都是:
二、datetime和timestamp区别
- datetime、timestamp精确度都是秒,
- datetime与时区无关,存储的范围广(1001-9999),
- timestamp与时区有关,存储的范围小(1970-2038)。
举例:查询表中所有内容
select * from table_name;
结果(当前时区为东八区):
修改mysql时区后再次查询:
set time_zone ='+0:00'; //修改时区
总结:可以发现时区变化之后,timestamp的值会相应的自动发生变化!