Mysql查询确定给定的datetime是否包含在datetime间隔中 [英] Mysql query to determine if the given datetime is included in the datetime interval

问题描述

我坚持这个任务，如果给定的datetime属于开始和结束日期之间。
例如，以下是日历数据表：

I'm stucked on this task where if the given datetime falls in between the start and end date. For example, here is a table of calendar data:

| id |  start               | end

| 31 | 2017-03-15 05:21:30  | 2017-03-15 06:21:30 
| 32 | 2017-03-14 06:25:30  | 2017-03-14 06:25:30 
| 33 | 2017-03-14 06:25:45  | 2017-03-14 06:25:45 

我必须在表中插入新数据以下开始和结束日期时间：

And I have to insert new data into the table with the following start and end datetimes:

开始：2017-03-15 05:30:30
end：2017-03-15 06:30:30

但在此之前，我必须检查开始和结束日期存在于我表中的任何日期时间间隔。

But before that I have to check if the start and end date is existing in any datetime intervals in my table.

结果应该是给定的开始和结束日期不会被插入，因为它落在包括在表id 31 datetime间隔之间。

The result should be that the given start and end dates will not be inserted because it falls or it is included between the table id 31 datetime interval.

我应该如何使用查询？我尝试了这种查询：从calendar_date选择*作为c其中c.start> = start AND c.end <= end
确定给定的开始和结束日期时间存在，但没有找到结果，我想我搞砸了查询。我真的希望有人可以帮我检查一下。

How should I be able to do that using a query? I tried this kind of query: Select * from calendar_date as c where c.start >= start AND c.end <= end to determine that the given start and end datetime exists but no results found and I think I messed up with the query. I really hope that someone could help me check this out.

推荐答案

你去：

Select * from calendar_date as ,c where LEAST(c.end, end) - GREATEST(c.start, start) > 0

这篇关于Mysql查询确定给定的datetime是否包含在datetime间隔中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！