# How to test if one range of dates overlaps another?

**URL:** <https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274>\
**Category:** Rails\
**Created:** [January 16, 2008, 10:29pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274 "2008-01-16T22:29:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [January 16, 2008, 10:29pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274/1 "2008-01-16T22:29:49Z")

</div>

Hi all,

I have a situation with a recurring billing application where I need  
to test if one range of dates “overlaps” another range of dates. Does  
anyone know a rails find or mysql select statement that will test for  
this? Here are the details:

- Recurringcharge items have a start\_date and an end\_date that  
represent the period of time that the charges should occur.
- Statement items have begin\_on and end\_on properties that represent  
the period of time that is being billed for.

In order to create a statements, I need to see which recurringcharges  
occur within the statement period.

I’ve been racking my brains, but can’t seem to come up with the  
elegant way to do this. Any ideas?

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [January 16, 2008, 10:47pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274/2 "2008-01-16T22:47:57Z")

</div>

On 16 Jan 2008, at 21:28, Neal L wrote:

> - Statement items have begin\_on and end\_on properties that represent  
> the period of time that is being billed for.
> 
> In order to create a statements, I need to see which recurringcharges  
> occur within the statement period.

What’s the input ? if the input is a given statement item (ie a begin  
& end time) then it’s just  
RecurringCharge.find :all, :conditions =\> [‘start\_date \<= ? AND  
end\_date \>= ?’, end\_on, begin\_on]

Fred

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [January 16, 2008, 10:55pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274/3 "2008-01-16T22:55:16Z")

</div>

Fred,

Thanks for the quick reply, but it’s not quite that simple. A  
recurring charge has a defined start and end date. If the statement  
is being generated within the start and end dates, then the simple  
test you suggested would work just fine. The tricky part is when a  
recurring charge item only occurs during _part_ of a statement’s  
period.

For example, let’s say you have a recurring charge called Rent where  
start\_on = 1/1/2008 and end\_on = 12/31/2008. You want to generate a  
statement that covers the period from 12/15/2008 through 1/14/2009.

The Rent recurring charge does occur within the statement period, but  
the charge’s end\_on (12/31/08) is before the end\_on date of the  
statement ( 1/14/2009).

So what I really need to test for is overlap. Does this make sense?

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [January 16, 2008, 11:11pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274/4 "2008-01-16T23:11:28Z")

</div>

Solved my own problem… With a little inspiration from here:

> **[DEVShed](https://devshed.com)**
>
> Founded in 1997, DEVShed is the perfect place for web developers to learn, share their work, and build upon the ideas of others.

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [January 16, 2008, 11:27pm UTC](https://www.ruby-forum.com/t/how-to-test-if-one-range-of-dates-overlaps-another/126274/5 "2008-01-16T23:27:58Z")

</div>

On 16 Jan 2008, at 21:54, Neal L wrote:

> For example, let’s say you have a recurring charge called Rent where  
> start\_on = 1/1/2008 and end\_on = 12/31/2008. You want to generate a  
> statement that covers the period from 12/15/2008 through 1/14/2009.
> 
> The Rent recurring charge does occur within the statement period, but  
> the charge’s end\_on (12/31/08) is before the end\_on date of the  
> statement ( 1/14/2009).
> 
> So what I really need to test for is overlap. Does this make sense?

Which is exactly what the code I showed does 🙂  
The condition i suggested is [start\_date \<= ? AND end\_date \>= ?’,  
end\_on, begin\_on]

the rent’s start (1/1/2008) is certainly before the period end  
(1/14/2009) and the rent’s end(12/31/2008) is certainly \>= the period  
start (12/15/2008)  
To rephrase in english, a charge should be included if it starts  
before the end of the period and finished after its start.

Fred