Question 168407 in Mysql Question posted in
The official MySQL documentation can be found here.

Does MySQL respect logical grouping when querying?

I have the following table structure/data:

id date (DATE) hour (INT) tally (INT) sum (INT)
1 2023-05-15 7 12 2
2 2023-05-15 7 1 4

With the following query:

SELECT * FROM `table` WHERE
((`date` >= '2023-05-15') AND (`hour` >= '4')) AND ((`date` <= '2023-05-16') AND (`hour` <= '4')) ORDER BY `id` DESC LIMIT 0,1000;

My expectation is that the data in the example is returned. I have two discrete where conditions (parenthesised). Instead when I run the query no data is returned.

Could anyone point out why the data is not being returned?

Tags:

2

Answers


  1. 0

    Your query is the equivalent of this :

    SELECT * FROM `mytable` WHERE
`date` between '2023-05-15' and '2023-05-16'
AND 
`hour` >= 4 AND `hour` <= '4'
ORDER BY `id` 
DESC LIMIT 0,1000;

    Or more precisely this :

    SELECT * FROM `mytable` WHERE
`date` between '2023-05-15' and '2023-05-16'
AND 
`hour`= '4'
ORDER BY `id` 
DESC LIMIT 0,1000;

    Because you have no records with hour = 4, the result will be empty.

    Login or Signup to reply.
  2. 0

    What you’d need is this:

    SELECT
  *
FROM
  `table`
WHERE
  (
    (`date` = '2023-05-15' AND `hour` >= '4')
    OR
    (`date` > '2023-05-15')
  )
  AND
  (
    (`date` = '2023-05-16' AND `hour` <= '4')
    OR
    (`date` < '2023-05-16')
  )
ORDER BY
  `id` DESC
LIMIT
  0,1000

    Which is why you should never keep the date and time components separately.

    It’as like storing 1.25 as…

    units tenths hundredths
    1 2 5
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search