在开始日期和结束日期之间选择值 [英] Select values between start date and end date

问题描述

我有一个包含一些活动的表格，每个活动都有开始日期和结束日期。例如：
表eventos_sapo

I have a table with some events and each event have a start date and end date. For example: Table eventos_sapo

id   titulo imagem startDate endDate
1    ...    ...    2011-02-12   2015-02-12
2    ...    ...    2013-02-21   2015-02-12
3    ...    ...    2014-03-14   2015-02-12

我尝试一些查询来获得开始和结束日期之间的事件，我的网络应用程序，但没有一个查询返回与 id = 1 的事件。让我们说用户介绍开始 date = 2013-01-03 和结束 date = 2015-02-14 ，应该返回事件1,2和3，但是id = 1的事件不会被我的任何查询返回。

I try some queries to get the events between some start and end dates, introduced by the user through my web app, but none of the queries returns the event with id=1. Let's say that the user introduce the start date=2013-01-03and the end date=2015-02-14, should return the events 1,2 and 3, but the event with id=1 is not returned by none of my queries.

这里是我试过的： / p>

Here goes what I tried:

Select id from eventos_sapo where startDate >= '2013-01-03' AND endDate <= '2015-02-14'

Select id from eventos_sapo where startDate BETWEEN '2013-01-03' AND '2015-02-14'
AND endDate BETWEEN '2013-01-03' AND '2015-02-14'

我必须做什么，包括id = 1的事件，因为这个事件

What I have to do, to include the event with id=1, because this event is active.

推荐答案

我读这个问题是为了寻找范围的重叠：

I read this problem as looking for the overlap of ranges:

所以你有数据范围：T1到T3。并寻找与范围T2到T4的重叠。因为它们重叠，你想要。
但是，如果数据是T1到T3，范围是T4到T5 ..没有重叠，因此不想要。

so you have data in range: T1 to T3. And looking for overlap with range T2 to T4. Since they overlap, you want. However, if data is T1 to T3 and range is T4 to T5 .. no overlap so don't want.

您可以使用此查询：

Select id 
  from eventos_sapo 
 where startDate <= '2015-02-14'
   AND endDate >= '2013-01-03';

因此，如果它在给定日期（2015年2月14日）之后开始，则不包括。
或如果它在2013年1月3日之前结束...不包括它。
包含其他所有内容，因为它重叠：

So if it started after the given date (Feb 14, 2015), then don't include it. or if it ended prior to Jan 3, 2013 ... don't include it. Include everything else, since it overlaps:

例如：
数据在给定范围开始日期之后结束，数据开始日期因此它是以下情况之一：

As 1 example: Data ends after the given range start date, and the data start date is prior to end, so it's one of the following cases:

Data:           |---|
Given range:      |---|

Data:           |-------|
Given range:      |---|

Data:             |---|
Given range:    |---|

Data:             |---|
Given range:    |-------|

这篇关于在开始日期和结束日期之间选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！