The Dude The Dude - 5 days ago 5
MySQL Question

MYSQL Select Where Time Between Returns Rows Older Than Query

I have a mysql DB with a table called data,

mysql> describe data;
+-------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+-------------------+----------------+
| idx | int(11) | NO | PRI | NULL | auto_increment |
| ts | timestamp | NO | | CURRENT_TIMESTAMP | |
| id | tinyint(3) unsigned | NO | | NULL | |
| value | decimal(10,2) | YES | | NULL | |
+-------+---------------------+------+-----+-------------------+----------------+


When I try to select a specific date range, e.g., today, the query returns data that is out of the date range.

mysql> SELECT * FROM data WHERE ts BETWEEN '2016-11-27 00:11:00' AND '2016-11-29 00:11:00' AND id LIKE '0' OR id LIKE '1' ORDER BY ts ASC LIMIT 10;
+-------+---------------------+----+-------+
| idx | ts | id | value |
+-------+---------------------+----+-------+
| 14117 | 2016-11-12 15:24:16 | 1 | 0.00 |
| 20144 | 2016-11-16 20:03:50 | 1 | 56.00 |
| 20147 | 2016-11-16 20:04:10 | 1 | 52.00 |
| 20150 | 2016-11-16 20:05:10 | 1 | 52.00 |
| 20153 | 2016-11-16 20:06:11 | 1 | 52.00 |
| 20156 | 2016-11-16 20:07:11 | 1 | 52.00 |
| 20159 | 2016-11-16 20:08:17 | 1 | 52.00 |
| 20162 | 2016-11-16 20:09:18 | 1 | 52.00 |
| 20165 | 2016-11-16 20:10:21 | 1 | 52.00 |
| 20168 | 2016-11-16 20:11:27 | 1 | 52.00 |
+-------+---------------------+----+-------+


I'm very new to MYSQL so hopefully this is something easy to solve, but I haven't been able to find the solution (not for lack of trying).

Answer

The problem is the OR. You can use parentheses, or just use IN:

SELECT *
FROM data
WHERE ts BETWEEN '2016-11-27 00:11:00' AND '2016-11-29 00:11:00' AND
      id IN (0, 1) 
ORDER BY ts ASC
LIMIT 10;
Comments