[adsense id=”0514458240″ width=”468″ height=”60″]
The BETWEEN syntax does make a query more readable. For instance, give me all ages between 3 and 5 could be written:
SELECT * FROM table WHERE ages BETWEEN 3 AND 5;
This is very readable. However, if you are using the syntax with a DATETIME then you need to think carefully about the query and its meaning. The DATETIME type will store both the date and the time. For example, 12th of January 2015 at 13:00. If you were to issue the statement:
mysql> insert into persons (dob, name) values (str_to_date('01/01/2015 00:00', '%d/%m/%Y %T'), 'Tom'),
-> (str_to_date('01/01/2015 01:00', '%d/%m/%Y %T'), 'Tim'),
-> (str_to_date('02/01/2015 15:23', '%d/%m/%Y %T'), 'Ron'),
-> (str_to_date('07/01/2015 10:19', '%d/%m/%Y %T'), 'Jerry'),
-> (str_to_date('07/01/2015 21:59', '%d/%m/%Y %T'), 'Abe'),
-> (str_to_date('11/01/2015 16:00', '%d/%m/%Y %T'), 'Jake'),
-> (str_to_date('12/01/2015 13:32', '%d/%m/%Y %T'), 'John'),
-> (str_to_date('13/01/2015 21:37', '%d/%m/%Y %T'), 'Jack');
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from persons where dob between str_to_date('01/01/2015', '%d/%m/%Y') and str_to_date('12/01/2015', '%d/%m/%Y') order by dob;
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
+----+---------------------+-------+
6 rows in set (0.01 sec)
The record of the 12th is not selected. This is because the STR_TO_DATE is creating a DATETIME with 00:00 as the time element. So between 1st of January 2015 00:00 and 12th of January 2015 00:00 will not return the row of the 12th of January.
To correct this you can either rewrite the query as follows including the time component:
mysql> select * from persons where dob between str_to_date('01/01/2015', '%d/%m/%Y') and str_to_date('12/01/2015 23:59:59', '%d/%m/%Y %T') order by dob;
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
+----+---------------------+-------+
7 rows in set (0.00 sec)
or alternatively rewrite the query using greater or equal to and less than the next highest day (in our example the 13th of January 2015)
mysql> select * from persons where dob >= str_to_date('01/01/2015', '%d/%m/%Y') and dob < str_to_date('13/01/2015', '%d/%m/%Y') order by dob;
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
+----+---------------------+-------+
7 rows in set (0.00 sec)
There is another alternative which is to convert each dob field into %Y%m%d value and then compare as follows:
mysql> select * from persons where str_to_date(date_format(dob, '%Y%m%d'), '%Y%m%d') between str_to_date('01/01/2015', '%d/%m/%Y') and str_to_date('12/01/2015', '%d/%m/%Y');
+----+---------------------+-------+
| id | dob | name |
+----+---------------------+-------+
| 1 | 2015-01-01 00:00:00 | Tom |
| 2 | 2015-01-01 01:00:00 | Tim |
| 3 | 2015-01-02 15:23:00 | Ron |
| 4 | 2015-01-07 10:19:00 | Jerry |
| 5 | 2015-01-07 21:59:00 | Abe |
| 6 | 2015-01-11 16:00:00 | Jake |
| 7 | 2015-01-12 13:32:00 | John |
+----+---------------------+-------+
7 rows in set (0.00 sec)
However, be aware that the last query has 2 effects:
- Each record performs a double conversion, the date to a string and the string back to a date. There is considerable CPU load when performing the conversions so the more content that needs to be converted the longer the query will take and the more resources it will use
- Any index created on the dob field will not be used because of the conversions