处理开放间隔时，发现如果给定的日期范围是在另一个日期范围内?

Question

我正在尝试获得用户在特定的7天时间段内启用他/她的帐户的秒数。

这可以很容易地完成使用TIMEDIFF-但什么使这个棘手(不回答其他任何地方):

[A]该帐户在我们的时间段开始(id2)时被启用，并且应该被认为自开始日期以来已启用。

[B]没有enabled_end设置id5，这意味着帐户当前是启用的，应该被认为是启用的，直到结束日期。

她是基本查询:

SELECT SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(enabled_end, enabled_start)))) AS total_seconds from logs WHERE user_id = '123' and enabled_start >= '2022-04-22 00:00:00' and enabled_end <= '2022-04-27 23:59:59'

所以上面的查询将不包括[A]中描述的id2和[B]中描述的id5

<表类> id user_id enabled_start enabled_end tbody><<tr>51232022-04-26 12:13:38零(=账户仍然启用)41232022-04-25 15:22:362022-04-25 17:32:1131232022-04-24 11:16:462022-04-25 05:10:0821232022-04-15 14:44:002022-04-23 10:58:5311232022-03-29 16:44:152022-04-04 11:22:3901232022-03-24 13:44:152022-03-25 09:11:39

使用

TIMEDIFF(LEAST(COALESCE(enabled_end, TIMESTAMP '2022-04-27 23:59:59'), TIMESTAMP '2022-04-27 23:59:59'))

• 如果enabled_end为NULL，则COALESCE返回范围结束。
• 如果enabled_end超出范围，则LEAST返回范围结束。
• 在所有其他情况下返回enabled_end值。

TIMESTAMP指定符导致日期时间比较。您可以删除它-在这种情况下，值将作为字符串进行比较(在这种情况下，datetime字面值必须是完整的，即它必须包含1位数部分值的前导零)。

正如@Akina指出的:

• 如果enabled_end为NULL，则COALESCE返回范围结束。
• 如果enabled_end不在范围内，则LEAST返回范围结束。
• 在所有其他情况下返回enabled_end值。
• 如果enabled_start不在范围内，则GREATEST返回范围开始。

然而，这并没有解决我的问题[A]中的问题，即一旦我们的时间段开始(id 2)，该帐户就被启用了，应该被认为是从开始日期开始启用的。

我发现了两种不同的方法来确定两个日期范围是否重叠:

• (StartA <= EndB) and (EndA>= StartB)
• 替代方式:如果两个段[a, b]和[c, d]相交:(a-d)*(b-c) <= 0

同样，如果enabled_end为NULL，我们只希望在enabled_start小于结束日期时包含它。

所有这些都导致了这个查询(确保日期是Y-m-d H:i:s格式)-像魅力一样工作!

SELECT sum(TIMESTAMPDIFF(SECOND, 

GREATEST(enabled_start, TIMESTAMP '2022-04-22 00:00:00'),

LEAST(COALESCE(enabled_end, TIMESTAMP '2022-04-28 00:00:00'), TIMESTAMP '2022-04-28 00:00:00'))

) as total_seconds FROM logs 
WHERE
((enabled_start <= '2022-04-28 00:00:00') and (enabled_end >= '2022-04-22 00:00:00') OR
enabled_end IS NULL and enabled_start < '2022-04-28 00:00:00')
and enabled_start IS NOT NULL

and user_id = '123' group by user_id