關于MySQL資料庫如何按時間查詢

這裡做了幾個測試

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為資料庫的時間字段

轉載于:https://www.cnblogs.com/onlymate/p/4789832.html