MySQL – How to Include All Records Between Two Dates

dateMySQL

I have a table with the columns ID, start_time, and end_time.
I would like to extract all the records that are active during a date range.

  ID | start_time |  end_time  
 ----|------------|------------ 
   1 | 2019-07-22 | 2019-07-25 
   2 | 2019-07-20 | 2019-07-25 
   3 | 2019-07-20 | 2019-07-21 
   4 | 2019-07-20 | 2019-07-25 
   5 | 2019-07-22 | 2019-07-22 
   6 | 2019-07-25 | 2019-07-25 
   7 | 2019-07-26 | 2019-07-27

Example Query parameters

  • start: 2019-07-22
  • end: 2019-07-25

Expected results (5 rows):

 ID | start_time |  end_time 
----|------------|------------
  1 | 2019-07-22 | 2019-07-25
  2 | 2019-07-20 | 2019-07-25
  4 | 2019-07-20 | 2019-07-25
  5 | 2019-07-22 | 2019-07-22
  6 | 2019-07-25 | 2019-07-25

the row containing ID 7 is omitted, because it is not active in the specified date range

I want to include the records with start_time 2019-07-20 because they are actually active on July 22.

If I use the following:

where start_time >= '2019-07-22' and end_time <= '2019-07-25'

…then I am excluding the records starting on July 20.

How can I include those records?

Best Answer

So if you're trying to find all active/valid records in a time frame, and you are using intervals, then you must use the following criteria:

  1. End date was on/after the start of the window
  2. Start date was on/before the start of the window

This is working on the assumption that a record is still effective as of the end date, as indicated in your example.

So your query becomes:

SELECT
  Id
 ,start_time
 ,end_time
FROM
  <your table>
WHERE
  start_time <= '2019-07-25'
    AND end_time >= '2019-07-22'

A simple truth table helps verify:

Id  | Start_Time  | End_Time    | Start <= 2019-07-25 | End >= 2019-07-22
1   | 2019-07-22  | 2019-07-25  | Y                   | Y
2   | 2019-07-20  | 2019-07-25  | Y                   | Y
3   | 2019-07-20  | 2019-07-21  | Y                   | N
4   | 2019-07-20  | 2019-07-25  | Y                   | Y
5   | 2019-07-22  | 2019-07-22  | Y                   | Y
6   | 2019-07-25  | 2019-07-25  | Y                   | Y
7   | 2019-07-26  | 2019-07-27  | N                   | Y

Other notes: Those are dates, not times. Column should be Start_Date/End_Date or Start_Datetime/End_Datetime if you are include hours/minutes/seconds (you can abbreviate with Dt/Dtm if you wish).

From comment:

I assume that if I want to filter only for the startdate, I can do WHERE end_time >= '2019-07-22 00:00:00' But I am not sure about query only for the enddate. What is the correct condition? WHERE start_time <= '2019-07-25 23:59:59' OR WHERE '2019-07-25 00:00:00' BETWEEN start_time AND end_time

So here we need to be careful with what question is actually be asked, and what answer the query is providing.

The statement WHERE end_time >= '2019-07-22 00:00:00' is the same as saying: any records that end OR start on/after 2019-07-22 00:00:00.

The inverse of that statement would be any records that start AND end before 2019-07-22 00:00:00, which is WHERE end_time < '2019-07-22 00:00:00'.

So if your question is actually "Which records end or start on/before 2019-07-22 00:00:00 the condition is WHERE start_time <= 2019-07-22 00:00:00

Related Question