Query an SQL table for dates that start/end within a range, and dates that fall within the range but start before/after the range

Question

Overview:

I have a SQL database table where each row (event) contains a start and end date formatted as a timestamp.

Target:

Show events for a given week, these include events that start and end within that week, but may also take place within that week (their start/end dates may be within that week, or they may be before that week begins and ends after that week). Example of expected result:

• For a given week of July 16th to July 22nd, the results will include the following events:
  • Events that begin and end on July 16, July 19, or July 22.
  • Events that started on July 10th and ended on July 19th.
  • An event that started on July 19th and ended on July 26th.
  • Events starting on July 4th and ending on July 30th.

I've tried using a BETWEEN query and a query using start > {$week_start} AND end < {$week_end}, but neither method takes into account "overlapping" dates.

Question: Is it possible to write a single query that covers all these cases?

BTW, if there is a better solution, I would accept using a date format other than timestamp.

Answer 1

The logic you are looking for is:

start <= {$week_end} AND end >= {$week_start}