Geek Answers Handbook

How to determine if a date range exists at any time within a different date range?

I have an event table that sets a date range with the start_date and end_date . I have a different date range specified in the code that defines the current week as "week_start" and "week_end".

I want to request all events for the week. Cases look like this:

  • The event begins and ends within a week.
  • The event starts before the week, but ends during the week.
  • The event starts within a week, but ends in a week.
  • The event starts before the week, and also ends after a week.
  • Events that do not reside within and do not overlap the week are ignored.

I am trying to find a query that can handle all of these cases. So far I have managed to get only cases related to the overlap of the week or events that are completely internal; In fact, there are too many records or nothing at all.

+6
database datetime logic
source share
5 answers
 (event.start BETWEEN week.start AND week.end) OR (week.start BETWEEN event.start AND event.end)

In simple words, either the week begins during the event, or the event begins during the week.

Let me check:

Event starts and ends during the week.

The event starts within a week.

The event starts before the week, but ends during the week

Week starts during the event.

The event starts during the week, but ends after the week.

The event starts within a week.

The event starts before the week, and also ends after the week

Week starts during the event.

Note that BETWEEN in the above expressions is used for brevity only.

The strict expression is as follows:

 (event.start >= week.start AND event.start < week.end) OR (week.start >= event.start AND week.start < event.end)

provided that week.end is week.start + INTERVAL 7 DAY .

I. e. if you start the week with Sun, 0:00:00 , then it should end at next Sun, 0:00:00 (not Sat, 0:00:00 )

This expression looks more complex than commonly used:

 event.start < week.end AND event.end > week.start

but the first is more efficient and convenient for indexing.

See these blog posts for performance comparison:

+21
source share

You can write your condition as follows:

 start_date <= week_end AND end_date >= week_start

Edit: this suggests that start_date <= end_date and week_start <= week_end (are ordered correctly) and give better performance for most db implementations due to the fact that ORs are not used (which in some databases may cause performance problems)

Edit2: this solution also solves the problem of events that start before the interval and end after the interval.

+3
source share

+1 for pop Catalin, but alas, I don't have a say.

The constraint condition that you want is the standard way to express the Allen operator "OVERLAPS".

Additional SQL support: if end_date is NULL, remember to treat the zeros in these columns as “end of time”.

Additional functional disclaimer: Remember to adapt the use of '<=' versus '<' for whether recorded time periods are included in the end date or not.

+2
source share

OK...

 where start_date >= week_start and end_date <= week_end where start_date <= week_start and end_date >= week_start and end_date <= week_end where start_date >= week_start and start_date <= week_end and end_date > week_end where start_date < week_start and end_date > week_end
0
source share

(end2> = start1) && & & (start2 <= end1) I think it will return true for any overlapping date ranges.

I found a discussion about this here , which I found useful.

0
source share

More articles:


All Articles