SQL中的日期范围交集 [英] Date range intersection in 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.

如何在MySQL中执行此操作？

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:

一些行：

ROW  START  STOP
1    1010   1240
2     950   1040
3    1120   1121

我们想知道这些行在1030和1100之间的总和时间。

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

所以这个例子中的和是80。

So the sum in this example is 80.

推荐答案

假设@range_start和@range_end为您的条件参数，则在第一行中表示为70

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

：

SELECT SUM( LEAST(@range_end, stop) - GREATEST(@range_start, start) )
FROM Table
WHERE @range_start < stop AND @range_end > start

< /5.1/en/comparison-operators.html#function_greatestrel =nofollow noreferrer> greatest / least and 日期函数，您应该能够获得所需的日期类型直接操作。

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

这篇关于SQL中的日期范围交集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！