Geek Answers Handbook

How to compare overlapping values ​​inside a string?

I seem to have a problem with this SQL query:

SELECT * FROM appts 
WHERE timeStart >='$timeStart' 
AND timeEnd <='$timeEnd' 
AND dayappt='$boatdate'

Time is formatted as wartime. Logistics is that boat rental can be reserved at 7 a.m. to 1 p.m. or 9 a.m. to 1 p.m. or 9 a.m. to 5 p.m. If there is appt in this range, it should return appt, but it turned out to be inconsistent. If I choose 9 AM to 1 PM, he will ignore the app that starts from 7 AM, even if they overlap from 9 AM to 1 PM. If I choose 9 to 5, it will not return anything, even if it is necessary from 7 am to 1 pm. How to create an SQL statement that includes the entire range from timeStart to timeEnd, including those that overlap?

+3
source share
4 answers

A valid check would look like this:

SELECT * FROM appts 
WHERE timeStart <='$timeEnd' 
AND timeEnd >='$timeStart' 
AND dayappt='$boatdate'

Other good explanations have been given, but I will continue and update it with an alternative explanation of how I visualize this myself. Most people look for all possible matches, given two periods of time, they try to think of every combination of beginning and end that can impose a match on the meeting. I think about it when the two time periods do not overlap, which for some reason is easier for me.

Tell me, the time period that I check today, I want to find any period of time that does not overlap today. There are only two scenarios for this: either the time period starts after today (PeriodStart> EndOfToday), or the time period ends until today (PeriodEnd <StartOfToday).

, :
 (PeriodStart > EndOfToday) (PeriodEnd < StartOfToday)

, :
(PeriodStart <= EndOfToday) (PeriodEnd >= StartOfToday)

-Shane

+6

:

, OR.

SELECT * FROM appts 
WHERE (timeStart >='$timeStart' 
OR timeEnd <='$timeEnd')
AND dayappt='$boatdate'

, , , , -:

- @ShaneD . , 05:00 06:00, , . 18:00 .

:

?

- . , :

,

  • 7 1
  • 9:00 13:00
  • 9 5 .

, , . 9 1 ,...

. . , , hh: mm; , hh: mm: ss .

Appointments

Row     timeStart   timeEnd   Note
  1     07:00       13:00     First valid range
  2     09:00       13:00     Second valid range
  3     09:00       17:00     Third valid range
  4     14:00       17:00     First plausibly valid range
  5     05:00       06:00     First probably invalid range
  6     18:00       22:30     Second probably invalid range

, 09:00 13:00, Shahkalpesh () :

SELECT * FROM Appointments
    WHERE (timeStart >= '09:00' OR timeEnd <= '13:00')

. 1, 2, 3 09:00 13:00. 1, 2 3 , Shahkalpesh . , 4 (, , ) , . , 5 6 - - . [, timeStart <= timeEnd ( NULL, ), , Shahkalpesh 09: 00-13: 00, 09:00 13:00 , . 1 = 1 WHERE.]

ShaneD ( ):

SELECT * FROM Appointments
    WHERE timeStart <= '13:00' AND timeEnd >= '09:00'

, 1, 2 3, 4 ( timeStart > '13: 00 '), 5 ( timeEnd < '09: 00') 6 ( timeStart > '13: 00 '). , , "", "" "" (. " ", ) . ' > =' '< =' , .

+8

, .

, " " ( timeStart <= $timeStart, timeStart <= $timeEnd). , /, . , , , , .

, , .

0

, OR.


SELECT * FROM appts 
WHERE (timeStart >='$timeStart' 
OR timeEnd &LT;='$timeEnd')
AND dayappt='$boatdate'

, . , 1 .

-5

More articles:


All Articles