Determine if a time is within Time Range

We know 1pm is within time range 11am-2pm, also 11pm is within 10pm-2am.

And we need to know how to do that in code, sql, etc…

===== [ Preparation ] =====

Assume the followings are true for later comparison:

  1. No date involved in time comparison.
  2. 24-hours time format is in use.

Basically there are three kinds of time range we need to cater when compare with time:

  1. end time > start time. Eg 0000 – 2359, 0800 – 1800 (working hrs…)
  2. end time = start time. Eg 0100-0100, 1530-1530. Means only need that moment.
  3. end time < start time, cross midnight time range. Eg 1200 – 0300, 2000 – 0600 (sleeping hrs…)

===== [ How it works ? ] =====

Scenario 1 and 2 quite straight forward. To compare it, see example:

Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN StartTime AND EndTime

-- Result return if StartTime = 080000 and EndTime = 180000

However above example will give wrong outcome for scenario 3 because EndTime always less than StartTime, therefore no result will return.

To compare time in scenario 3, lets take time range 20:00 – 06:00 as example.

Firstly, we need to divide the time range at time 00:00, so will get two time range which is before 00:00 (2000 – 0000) and after 00:00 (0000 – 0600).

Secondly, we have a time T1 to be compare, and we know the time T1 will fall in three ranges, they are 2000-0000 (just name it TR1), 0000 – 0600 (TR2) and 0600 – 2000 (TR3).

So we have:

TR1 = 2000 – 0000

TR2 = 0000 – 0600

TR3 = 0600 – 2000

* Notice that original time range is 2000 – 0600, but we split it to 2000 – 0000 and 0000 – 0600. 0600 – 2000 is the time range that never mention and not cover by original time range 2000 – 0600.

* TR = Time Range

For TR1, we can replace the ‘0000’ with ‘2400’ since we know TR1 is taking time range before midnight. End up we have

TR1 = 2000 – 2400

TR2 = 0000 – 0600

TR3 = 0600 – 2000

Things become straight forward now. Just need to check whether time T1 fall within TR1, TR2 or TR3. Notice that we can make a simple time comparison for TR1, TR2 and TR3, like what we did in scenario 1 and 2. Refer to sample code below:


Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN

 -- Start Time
 (CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then '000000' Else StartTime End) ELSE StartTime END)
 AND
 -- End Time
(CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then EndTime Else '240000' End) ELSE EndTime END)

As we can see in last sample code, it only take TR1 or TR2 for comparison. A simple checking still needed to determine which time range is use for comparison.

We can summarize the last sample code into following:


if (endTime < startTime)  // This is cross midnight time range
   if (compareTime <= endTime)
      // compareTime is between '000000' and endTime?
   else
      // compareTime is between startTime and '240000'?
else
   // compareTime is between startTime and endTime?

~~~ * ~~~ * ~~~

Keyword: Check time range.  How to determine time, time period.

Advertisements