Select Between Date Range Mysql

Question

I want to select records between 2 dates:

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

No results. If I do:

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

Many results appear.Why?

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)

My engine is TokuDB.

• András Váczi: @oNare how is this different from the OP's second query?
• András Váczi: Could you show some of those many? Or put some of your data in this fiddle to reproduce your issue (after it, post the new SQLFiddle link here)?
• gdm: see the my edit
• gdm: I forgot to say that my engine is TokuDB.
• Vérace: I know this is answered, but for future reference, could you post the results of SHOW CREATE TABLE Orders\G instead of DESCRIBE Orders - it gives output that you can cut and paste, unlike DESCRIBE.

Answer 1

In your first query you do ...orders.created <= '2015-07-31'; which is actually

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

since you have a timestamp or datetime datatype. Either use the DATE() function like you did in your other attempt, or write it like

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

• jkavalik: Always use the second one. DATE() can not use index. (And you can tailor the datetime for some timezone modifications where DATE() cannot do that
• ypercubeᵀᴹ: And even better to use < '2015年08月01日'; to avoid any lost rows when/if you have microsecond accuracy in the column.

Answer 2

Better yet:

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

This works for DATE, DATETIME, DATETIME(6), leap year, etc. (Notice the <, not <=.)