datetime类型
CREATE TABLE `test_date` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
‘create_time’为datetime类型
select * from test_date where create_time BETWEEN '2018-10-16' AND '2018-10-28'
查询出来的数据,包含‘2018-10-28 00:00:00 ’,不包括' 2018-10-28 01:00:00 '
如果是datetime的'2018-10-28' 会被转成'2018-10-28 00:00:00'类型,所以查不出来2018-10-28的数据。
可以通过精确到秒查
select * from test_date where create_time BETWEEN '2018-10-16 00:00:00' AND '2018-10-28 23:59:59'
date类型
CREATE TABLE `test_date` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`create_time` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8;
date类型
select * from test_date where create_time BETWEEN '2018-10-16' AND '2018-10-28'