1

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.

  • @oNare how is this different from the OP's second query? – dezso Aug 3 '15 at 12:51
  • 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)? – dezso Aug 3 '15 at 12:52
  • see the my edit – giuseppe Aug 3 '15 at 12:57
  • I forgot to say that my engine is TokuDB. – giuseppe Aug 3 '15 at 13:06
  • 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. – Vérace Aug 3 '15 at 15:45
2

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';
  • 1
    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 – jkavalik Aug 3 '15 at 13:20
  • 1
    And even better to use < '2015-08-01'; to avoid any lost rows when/if you have microsecond accuracy in the column. – ypercubeᵀᴹ Aug 3 '15 at 13:50
1

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 <=.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.