在日期範圍之間選擇 Mysql

我想選擇兩個日期之間的記錄：

select * from orders where orders.created  <= '2015-07-31';
select * from orders where orders.created >= '2015-07-01' and orders.created  <= '2015-07-31';

沒有結果。如果我做：

select * from orders where date(orders.created) >= '2015-07-01' and date(orders.created)  <= '2015-07-31';

出現很多結果。為什麼？

mysql> select id,identifier,created from orders where date(orders.created) >= '2015-07-01' and date(orders.created)  <= '2015-07-31';
+-----+--------------+---------------------+
| id  | identifier   | created             |
+-----+--------------+---------------------+
| 366 | eu-126123916 | 2015-07-31 16:58:22 |
| 367 | eu-126127446 | 2015-07-31 17:43:14 |
| 368 | eu-126127510 | 2015-07-31 17:43:52 |
| 370 | eu-126134761 | 2015-07-31 18:56:07 |
| 371 | eu-126135814 | 2015-07-31 19:05:46 |
| 372 | eu-126144955 | 2015-07-31 20:34:48 |
+-----+--------------+---------------------+
6 rows in set (0.00 sec)


mysql> describe orders;
+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra          |
+---------------------+---------------------+------+-----+---------+----------------+
| id                  | bigint(19) unsigned | NO   | PRI | NULL    | auto_increment    
| total               | float               | YES  |     | NULL    |                |
| totaltax            | float               | YES  |     | NULL    |                |
| totalpaid           | float               | YES  |     | NULL    |                |
| ticket              | varchar(45)         | YES  |     | NULL    |                |
| paypal_id           | varchar(45)         | YES  |     | NULL    |                |
| note                | text                | YES  |     | NULL    |                |
| totaldocuments      | int(4)              | YES  |     | NULL    |                |
| sent_to_operator    | varchar(1024)       | YES  |     | NULL    |                |
| created             | datetime            | YES  |     | NULL    |                |
| modified            | datetime            | YES  |     | NULL    |                |


22 rows in set (0.01 sec)

我的引擎是 TokuDB。

在您的第一個查詢中，您...orders.created <= '2015-07-31';實際上是

orders.created  <= '2015-07-31 00:00:00';

因為您有時間戳或日期時間數據類型。要麼DATE()像在其他嘗試中那樣使用該功能，要麼像這樣寫

orders.created  <= '2015-07-31 23:59:59';

更好的是：

where orders.created >= '2015-07-01'
 and orders.created  < '2015-07-01' + INTERVAL 1 MONTH;

這適用於 DATE、DATETIME、DATETIME(6)、閏年等。（注意<，不是<=。）

引用自：https://dba.stackexchange.com/questions/108858