Get records between time range on 24 hours

Question

I try to find pricing in my table according hours and duration. For example, this is my table

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  1 |               1 | 22:00:00   | 21:59:59 |     1.5 |
|  2 |               2 | 05:30:00   | 16:29:00 |    0.75 |
|  3 |               2 | 16:30:00   | 18:59:59 |    0.50 |
|  4 |               2 | 19:00:00   | 05:29:59 |     1.5 |
+----+-----------------+------------+----------+---------+

Now, i've to find for subcription 2 that start at 16:00:00 and finish at 20:00:00 I tried several things like this without success

SET @startTime := '17:30:00';
SET @endTime := '20:30:00';

SELECT *
FROM subscription_detail_pricing sdp
WHERE (
    (start_time < end_time AND TIME(@startTime) BETWEEN start_time AND end_time)
    OR
    (start_time > end_time AND TIME(@startTime) NOT BETWEEN end_time AND start_time)
    OR
    (start_time < end_time AND TIME(@endTime) BETWEEN start_time AND end_time) 
    OR
    (start_time > end_time AND TIME(@endTime) NOT BETWEEN end_time AND start_time)
) AND subscription_id = 2;

I don't find query method for get my 3 records corresponding to this range

If someone can give some tips, i'm loosing myself -_-

Thanks for reading BenWa

Answer 1

This looks like a classic range overlap query..

If possible, you could save yourself a headache, split any ranges that wrap over "00:00:00" into two separate rows and make your end_times exclusive

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  1 |               1 | 00:00:00   | 24:00:00 |     1.5 |
|  2 |               2 | 00:00:00   | 05:30:00 |     1.5 |
|  3 |               2 | 05:30:00   | 16:30:00 |    0.75 |
|  4 |               2 | 16:30:00   | 19:00:00 |    0.50 |
|  5 |               2 | 19:00:00   | 24:00:00 |     1.5 |
+----+-----------------+------------+----------+---------+

Then your query becomes a lot simpler

SELECT *

  FROM subscription_detail_pricing

 WHERE start_time < "20:00:00"
   AND end_time > "16:00:00"

Note, I prefer exclusive comparisons, < and >, for this query because of what you are comparing

• start_time < "20:00:00" - comparing the start_time (inclusive) with the end of the inputted range "20:00:00" (exclusive)
• end_time > "16:00:00" - comparing the end_time (exclusive) to the start of the inputted range "16:00:00" (inclusive)

As long as at least one of the values being compared is exclusive, checking for equality, <= or >=, doesn't make sense to me

Answer 2

To check for overlap in time ranges, given s and e as start and end of the defined ranges, and given b and t as the begin and terminate of the range we want to compare, normally this condition

 b <= e AND t > s

is sufficient to check for an overlap. But that only works for b<t and s<e. In the scenario presented, there's a wrinkle with the "wrap", row id=4, with e (end_time) before the s (start_time).

---

One approach to fixing the problem would to store only rows where start_time is less than end_time.

We could accomplish that by breaking row id=4 into two rows

+----+-----------------+------------+----------+---------+
| id | subscription_id | start_time | end_time | pricing |
+----+-----------------+------------+----------+---------+
|  4 |               2 | 19:00:00   | 24:00:00 |     1.5 |
|  5 |               2 | 00:00:00   | 05:30:00 |     1.5 |
+----+-----------------+------------+----------+---------+

Similarly, row id=1 could be specified as

|  1 |               1 | 00:00:00   | 24:00:00 |     1.5 |

or, if there's some reason we need 22:00:00 as a start, we can break it into two ranges

| 1a  |              1 | 22:00:00   | 24:00:00 |     1.5 |
| 1b  |              1 | 00:00:00   | 22:00:00 |     1.5 |

With that approach:

SET @bt := '16:00:00' ;
SET @tt := '20:00:00' ;

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
     , ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ) AS overlap
  FROM sdp
 WHERE sdp.subscription_id = 2

returns:

  id  start_time  end_time   pricing  overlap  
----  ----------  ---------  -------  -------
   2  05:30:00.0  16:30:00.0    0.75        1
   3  16:30:00.0  19:00:00.0    0.50        1
   4  19:00:00.0  24:00:00.0    1.50        1
   5  00:00:00.0  05:30:00.0    1.50        0

---

backwards/wrap range overlap

If we have to handle the backwards range overlap, with the end_time before the start_time, then it seems like we can negate the same condition we used for the forward check, and add the check if the range is forward or backwards.

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
     ,     (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time )) AS f_overlap
     , NOT (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time )) AS b_overlap
  FROM sdp
 WHERE sdp.subscription_id = 2 

With the rows as shown in the original (with slight adjustment of end_time to be the first second outside of the range)...

  id  start_time  end_time    pricing  f_overlap  b_overlap  
----  ----------  ----------  -------  ---------  ---------
   2  05:30:00.0  16:30:00.0  0.75             1          0
   3  16:30:00.0  19:00:00.0  0.50             1          0
   4  19:00:00.0  05:30:00.0  1.50             0          1

The conditions we want to test are included in the SELECT list, that allows us to better see what is happening. We could also return the @bt and @tt in the same row.

Once we have those conditions tested, we can flip them down into the WHERE clause.

We need to OR those two conditions, because we have an overlap if either condition is true.

SELECT sdp.id
     , sdp.start_time
     , sdp.end_time
     , sdp.pricing
  FROM sdp
 WHERE sdp.subscription_id = 2
   AND (     (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ))
      OR NOT (sdp.start_time < sdp.end_time AND ( TIME(@bt) <= sdp.end_time AND TIME(@tt) > sdp.start_time ))
       )

NOTE: these conditions have not been sufficiently tested. note that these conditions require @bt to be before @et. If we want to handle a "backwards wrap" for those, then we need an additional condition checks and condition negation to handle that. We also want to be sure to test ranges with @bt of 00:00 and @et on or after 24:00.

---

for setup:

CREATE TABLE sdp (id INT, subscription_id INT, start_time TIME(1), end_time TIME(1), pricing DECIMAL(8,2), PRIMARY KEY (id))
;
INSERT INTO sdp (id, subscription_id, start_time, end_time, pricing) VALUES 
 ( 1 , 1 , '22:00:00'   , '22:00:00' ,  1.50 )
,( 2 , 2 , '05:30:00'   , '16:30:00' ,  0.75 )
,( 3 , 2 , '16:30:00'   , '19:00:00' ,  0.50 )
,( 4 , 2 , '19:00:00'   , '05:30:00' ,  1.50 )
;

to avoid wraps in ranges, make all ranges end_time after start_time

DELETE FROM sdp WHERE id = 4; 
INSERT INTO sdp (id, subscription_id, start_time, end_time, pricing) VALUES 
 ( 4 , 2 , '19:00:00'   , '24:30:00' ,  1.50 )
,( 5 , 2 , '00:00:00'   , '05:30:00' ,  1.50 )
;