CODEWITHSUNDEEP
mysql
The Dude
The Dude

Reputation: 1046

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

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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;

Upvotes: 2

Related Questions