Get Intersect Date and Time from DateRange List

Question

I am having n number of DateRange List (A,B, C...n). All lists has Start and End Date and Time. I want to get the intersect (common) date and time range from these list and generate slots in calender accordingly. For Example:

Start	End
15-03-2022 10:45 AM	16-03-2022 11 AM
16-03-2022 10:30 AM	17-03-2022 11 AM
16-03-2022 10:45 AM	19-03-2022 12 PM

Expected result:

Start	End
16-03-2022 10:45 AM	16-03-2022 11 AM

Thanks.

Answer 1

You seem to be asking for the overlapping range common to all the input ranges.

That's easily done with:

SELECT MAX(xstart) AS xstart, MIN(xend) AS xend
  FROM test
HAVING xstart <= xend
;

The maximum start date/time represents the only possible start of the overlapping range.

The minimum end date/time represents the only possible end of the overlapping range.

If the calculated maximum start is less than (or equal to) the minimum end, we have an overlap common to all inputs.

The "equal to" case is just a single date/time in common. If you don't consider that an overlap, change the logic to:

SELECT MAX(xstart) AS xstart, MIN(xend) AS xend
  FROM test
HAVING xstart < xend
;

If no overlap common to all is found, 0 rows are returned, instead of 1.

The fiddle (with MySQL)

The result:

xstart	xend
2022-03-16 10:45:00	2022-03-16 11:00:00