CODEWITHSUNDEEP
mysql
Acklavidian
Acklavidian

Reputation: 205

Using mysql to query dates that fall between 2 other dates

I have a table that has a field 'start_date' and also a field 'end_date'. Items are considered active when the current date is between 'start_date' and 'end_date'. How would I find all the items that would be considered active for the next 30 days.

Upvotes: 1

Views: 167

Answers (1)

spencer7593
spencer7593

Reputation: 108400

Assuming start_date and end_date are DATETIME, and you want to compare the date AND time components, you could do something like this:

SELECT a.*
  FROM atable a
 WHERE a.start_date >= NOW()
   AND a.end_date   <= NOW() + INTERVAL 30 DAYS

If start_date and end_date are DATE (rather than DATETIME), you probably only want to compare the DATE portion (without regard to a time component)

SELECT a.*
  FROM atable a
 WHERE a.start_date >= DATE(NOW())
   AND a.end_date   <= DATE(NOW()) + INTERVAL 30 DAYS

You may actually want a less than comparison (rather than less than or equal to) on the end date. You need to determine what results you want on that edge case. And the interval may need to be specified as 29 days, depending on how you define "the next 30 days".

Q: What about items that started before NOW and will run for the next 30 days?

A: I think I understand what you were asking.

I think you are wanting to retrieve any rows where the any point in time between the start_date and end_date falls anytime between now and 30 days from now. I can see that my query above does not answer that question.

For now, I'm going to consider only cases where " start_date < end_date ", and shelve cases where start_date is not less than end_date, or where either start_date or end_date or both are NULL.

We can depict the possible cases something (rather crudely) like this: The vertical bars represent NOW and NOW+30 The less than sign represents "start_date" The greater than sign represents "end_date" The dashes represent the range of DATETIME betwen start_date and end_date

      NOW     NOW+30
<--->  |       |              case 1: end_date < NOW()
   <---|---->  |              case 2: NOW() between start_date and end_date
    <--|-------|---->         case 3: NOW() > start_date and NOW+30 < end_date
       | <---> |              case 4: both start_date and end_date between NOW() and NOW()+30
       |    <--|--->          case 5: start_date between NOW() and NOW()+30 and end_date > NOW+30 
       |       |  <--->       case 6: start_date > NOW()+30


   <--->       |              case e1: end_date = NOW()
    <--|------->              case e2: start_date > NOW() AND end_date = NOW()+30
       <--->   |              case e3: start_date = NOW() and end_date < NOW()+30
       <------->              case e4: start_date = NOW() and end_date = NOW()+30
       <-------|-->           case e5: start_date = NOW() and end_date > NOW()+30
       |   <--->              case e6: start_date > NOW() AND end_date = NOW()+30
       |       <--->          case e7: start_date = NOW()+30

I think you are asking to return rows that satisfy cases 2 thru 5, which is all cases EXCEPT for case 1 and 6.

If we can write a predicate that tests for cases 1 and 6, and then negate that, it should give us what you want. Something like this:

To handle datetime with time component considered:

  WHERE NOT ( end_date < NOW() OR start_date > NOW() + INTERVAL 30 DAYS )

if start_date and end_date are DATE, to compare just the DATE portion, wrap the NOW() function in the DATE() function:

  WHERE NOT ( end_date < DATE(NOW()) OR start_date > DATE(NOW()) + INTERVAL 30 DAYS )

I shelved the oddball cases, of start_date > end_date, start_date = end_date, start_date IS NULL, end_date IS NULL, etc. I also omitted discussion of the edge cases (e1 thru e7), e.g. start_date = NOW(), end_date = NOW(), etc. For completeness, you probably want to check whether those cases are handled appropriately with this same predicate.

DOH!

It just dawned on me that this: 

 WHERE NOT ( a < n OR b > t )

is equivalent to this (at least for not null values)

 WHERE ( a >= n AND b <= t )

So this predicate should be equivalent:

 WHERE end_date >= NOW() AND start_date <= NOW() + INTERVAL 30 DAYS

Upvotes: 3

Related Questions