Mysql condition with same column

Question

I have the following table structure.

Table - Info

| day | hour | data |
| 2   |  3   |  A   |
| 3   |  5   |  B   |
| 4   |  1   |  B   |

Now I want to write a query to select the data as from day 2 hour 3 onwards to day 4 hour 1.

So I have written query something like below.

select * from Info 
where day >= 2 and hour >= 3 and day <= 4 and hour <= 1

But problem here is that it is comparing time of day 2 with hour 1 also.

Can anyone please help me out? I am stuck here.

It is noteworthy that the moment you use functions/expressions on the column in `WHERE` clause, the query becomes [non-sargeable](https://stackoverflow.com/a/799616/2469308). So, if performance is the criteria, you should look for solutions which are able to use indexing. — Madhur Bhaiya, Sep 12 '19 at 19:01

score 1 · Accepted Answer · answered Sep 12 '19 at 18:42

If day and hour mean what their name is then you can do it by converting everything to hours:

select * from Info 
where (day * 24 + hour) between (2 * 24 + 3) and (4 * 24 + 1)

To be more accurate the condition should be:

((day - 1) * 24 + hour) between ((2 - 1) * 24 + 3) and ((4 - 1) * 24 + 1)

but this does not affect the results.
See the demo.
Results:

| day | hour | data |
| --- | ---- | ---- |
| 2   | 3    | A    |
| 3   | 5    | B    |
| 4   | 1    | B    |

Sorry I forgot. Now I did it – Swapnil Koshti Sep 16 '19 at 12:00 — Swapnil Koshti, Sep 16 '19 at 12:00

score 0 · Answer 2 · answered Sep 12 '19 at 18:42

0

One option could be that you do a subquery and first select all records day >= 2 and hour >= 3 and then in in the outer query put day <= 4 and hour <= 1

answered Sep 12 '19 at 18:42

muasif80

5,586
4
32
45

Madhur Bhaiya · Answer 3 · 2019-09-12T18:53:41.977

A performant way (query which can utilize indexes (if defined)) will be considering three exclusive scenarios.

First scenario is that the day lies between (non-inclusive) the given start and end day values.

Second scenario would be that the day is same as the given start day value, and the hour value is >= to the given start hour value.

Third scenario would be that the day is same as the given end day value, and the hour value is <= to the given end hour value.

select * from Info 
where 
  (day > 2 AND day < 4)   /* First Scenario */
  OR 
  (day = 2 AND hour >= 3)  /* Second Scenario */
  OR 
  (day = 4 AND hour <= 1)  /* Third Scenario */

For good performance, please define the following Composite Index (day, hour)

Demo on DB Fiddle

Result

| day | hour | data |
| --- | ---- | ---- |
| 2   | 3    | A    |
| 3   | 5    | B    |
| 4   | 1    | B    |

Thank you. this is also useful for me. – Swapnil Koshti Sep 12 '19 at 19:12 — Swapnil Koshti, Sep 12 '19 at 19:12

Radagast · Answer 4 · 2019-09-16T13:34:32.837

0

Updated based on forpas's feedback

select * from info where ((day*24)+hour) between 51 and 97

edited Sep 16 '19 at 13:34

answered Sep 12 '19 at 19:29

Radagast

5,102
3
12
27

So from day = 1 and hour = 20 and to day = 5 and hour = 1 `between 120 and 51 will return nothing` – forpas Sep 12 '19 at 19:35
Thanks for catching that! – Radagast Sep 12 '19 at 19:40

Mysql condition with same column

4 Answers4