这里做了几个测试
select * from simingpai where TIMESTAMP(createTime) >= '2015-9-6';
select * from simingpai where TIMESTAMP(createTime) >= '2015-9-6 00:00:00';
select * from simingpai where TIMESTAMP(createTime) >= '2015-9-6 18:15:00';
select * from simingpai where DATE(createTime) >= '2015-9-6';
select * from simingpai where DATE(createTime) >= '2015-9-6 00:00:00';
select * from simingpai where DATE(createTime) >= '2015-9-6 00:00:01';
select * from simingpai where DATE(createTime) >= '2015-9-6 18:15:00';
这几条语法都没有问题，TIMESTAMP类型支持时分秒，DATE类型第一条和第二条输出结果相同（估计是忽略了后面的时分秒），但是第三条和第四条语句就会报错。应该是不支持时分秒。
select * from simingpai where TIMESTAMP(createTime) >= 2015-9-6;
select * from simingpai where TIMESTAMP(createTime) >= 2015-9-6 00:00:00;
select * from simingpai where TIMESTAMP(createTime) >= 2015-9-6 18:15:00;
select * from simingpai where Date(createTime) >= 2015-9-6;
select * from simingpai where DATE(createTime) >= 2015-9-6 00:00:00;
select * from simingpai where DATE(createTime) >= 2015-9-6 00:00:01;
select * from simingpai where DATE(createTime) >= 2015-9-6 18:15:00;
这里的SQL语句中TIMESTAMP类型和DATE类型的第一条语句能输出结果，其他的都不正确。这种格式应该不支持时分秒。
按照时间进行查询
查询当天的所有数据
select * from tg_winner where year(createTime)=year(now()) and month(createTime)=month(now()) and day(createTime)=day(now());
select * from tg_winner where to_days(createTime) = to_days(now());
select * from tg_winner where date(createTime) = curdate();
查询某天的所有数据
select * from tg_winner where DATE(createTime) = '2015-9-6';
select * from tg_winner where to_days(createTime) = to_days('2015-09-06');
select * from tg_winner where createTime = date_format(#{createTime},'%Y-%m-%d %H:%i:%s');
#{createTime}为Mybatis的.xml文件中实体类对应的Date类型的字段。即要传入的日期类型的变量
mysql查询 昨天 一周前 一月前 一年前的数据
mysql 昨天 一周前 一月前 一年前的数据 这里主要用到了DATE_SUB，
参考如下
SELECT * FROM tg_winner
where createTime>DATE_SUB(CURDATE(), INTERVAL 1 DAY)
where createTime>DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
where createTime>DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
where createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR
注意：如果数据库中时间以UNIX时间戳的形式存放的话，在时间对比上需要更改为统一格式：
DATE_SUB()返回的是格式化后的时间：2014-05-17
需要用UNIX_TIMESTAMP()转化为UNIX时间戳形式对比：
where createTime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
where createTime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
where createTime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
where createTime>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
createTime为数据库的时间字段