mysql 日期交集,SQL中的日期范围交集

I have a table where each row has a start and stop date-time. These can be arbitrarily short or long spans.

I want to query the sum duration of the intersection of all rows with two start and stop date-times.

How can you do this in MySQL?

Or do you have to select the rows that intersect the query start and stop times, then calculate the actual overlap of each row and sum it client-side?

To give an example, using milliseconds to make it clearer:

Some rows:

ROW START STOP

1 1010 1240

2 950 1040

3 1120 1121

And we want to know the sum time that these rows were between 1030 and 1100.

Lets compute the overlap of each row:

ROW INTERSECTION

1 70

2 10

3 0

So the sum in this example is 80.

解决方案

If your example should have said 70 in the first row then

assuming @range_start and @range_end as your condition paramters:

SELECT SUM( LEAST(@range_end, stop) - GREATEST(@range_start, start) )

FROM Table

WHERE @range_start < stop AND @range_end > start

using the greatest/least and date functions you should be able to get what you need directly operating on the date type.