I seem to have a problem with this SQL query:
我似乎有这个SQL查询的问题:
SELECT * FROM appts
WHERE timeStart >='$timeStart'
AND timeEnd <='$timeEnd'
AND dayappt='$boatdate'
The time is formatted as military time. The logistics is that a boat rental can be reserved at 7am til 1pm or 9am til 1pm or 9am til 5pm. If there is an appt within that range, it should return appts but it has proven to be inconsistent. If I pick 9am til 1pm, it will ignore appts that started with 7am even though it overlaps 9am-1pm. If I pick 9 to 5, it will return nothing even though it should with the 7am to 1pm. How do I make a SQL statement that includes the whole range from timeStart to timeEnd including those that overlap?
时间格式为军事时间。物流是租船可以在早上7点至下午1点至下午1点或上午9点至下午5点预订。如果在该范围内有appt,它应该返回appts,但事实证明它不一致。如果我选择上午9点直到下午1点,它将忽略从早上7点开始的应用程序,即使它在上午9点到下午1点重叠。如果我选择9到5,它将返回任何内容,即使它应该在早上7点到下午1点。如何创建包含从timeStart到timeEnd的整个范围的SQL语句,包括那些重叠的?
4 个解决方案
#1
The correct check would look like this:
正确的检查将如下所示:
SELECT * FROM appts
WHERE timeStart <='$timeEnd'
AND timeEnd >='$timeStart'
AND dayappt='$boatdate'
Other good explanations have been given but I'll go ahead and update it with an alternative explanation of how I visualize this myself. Most people are looking for each possible overlap, considering two time periods, they are trying to think of each combination of start and end that can make an appointment overlap. I think about it as when do two time periods not overlap which for some reason is easier for me.
已经给出了其他很好的解释,但我会继续更新它,并用我自己想象的另一种解释。大多数人都在寻找每个可能的重叠,考虑到两个时间段,他们试图考虑开始和结束的每个组合,这可以使约会重叠。我认为它是因为两个时间段不重叠,由于某种原因对我来说更容易。
Say the time period I am checking for is today, I want to find any time period that does not overlap today. There are really only two scenarios for that, either the time period starts after today (PeriodStart > EndOfToday) or the time period ends before today (PeriodEnd < StartOfToday).
说我正在检查的时间段是今天,我想找到今天不重叠的任何时间段。实际上只有两种情况,即时间段在今天之后开始(PeriodStart> EndOfToday)或时间段在今天之前结束(PeriodEnd
Given that we havea simple test for not overlapping:
(PeriodStart > EndOfToday) OR (PeriodEnd < StartOfToday)
鉴于我们有一个不重叠的简单测试:(PeriodStart> EndOfToday)OR(PeriodEnd
A quick flip around and you have a simple test for overlap:
(PeriodStart <= EndOfToday) AND (PeriodEnd >= StartOfToday)
快速翻转,你有一个简单的重叠测试:(PeriodStart <= EndOfToday)AND(PeriodEnd> = StartOfToday)
-Shane
#2
Shahkalpesh answered the question with:
Shahkalpesh回答了这个问题:
I think you need an OR.
我想你需要一个OR。
SELECT * FROM appts WHERE (timeStart >='$timeStart' OR timeEnd <='$timeEnd') AND dayappt='$boatdate'
I posted a comment that I consider this to be wrong, giving a pair of counter-examples:
我发表了一条评论,我认为这是错误的,给出了一对反例:
This is plain wrong - @ShaneD is correct. For example, this will pick out a booking between 05:00 and 06:00 because the actual end time is less than any of the end times you ask about. It will also pick up rentals from 18:00 onwards, for the equivalent reason.
这是完全错误的 - @ShaneD是正确的。例如,这将在05:00和06:00之间选择预订,因为实际结束时间少于您询问的任何结束时间。由于同等原因,它还将从18:00开始接收租金。
In a response to my comment, Shahkalpesh requested:
在回应我的评论时,Shahkalpesh要求:
Could you post a separate reply with data & input parameters with expected output?
您是否可以使用预期输出发布带有数据和输入参数的单独回复?
Fair enough - yes. Slightly edited, the question says:
足够公平 - 是的。略有编辑,问题说:
The logic is that a boat rental can be reserved
逻辑是可以预订船只租赁
- from 7am until 1pm, or
- from 9am until 1pm, or
- from 9am until 5pm.
从早上7点到下午1点,或
从上午9点到下午1点,或
从上午9点到下午5点。
If there is an appointment within that range, it should return appointments but it has proven to be inconsistent. If I pick 9am until 1pm, ...
如果在该范围内有预约,则应该返回预约,但事实证明它是不一致的。如果我选择上午9点到下午1点,...
Enough background. We can ignore the date of the appointments, and just consider the times. I'm assuming that there is an easy way to limit the times recorded to hh:mm format; not all DBMS actually provide that, but the extension to handle hh:mm:ss is trivial.
足够的背景。我们可以忽略约会的日期,只考虑时间。我假设有一种简单的方法可以将记录的时间限制为hh:mm格式;并非所有DBMS实际上都提供了这一点,但处理hh:mm:ss的扩展是微不足道的。
Appointments
Row timeStart timeEnd Note
1 07:00 13:00 First valid range
2 09:00 13:00 Second valid range
3 09:00 17:00 Third valid range
4 14:00 17:00 First plausibly valid range
5 05:00 06:00 First probably invalid range
6 18:00 22:30 Second probably invalid range
Given a search for appointments overlapping the range 09:00 - 13:00, Shahkalpesh's (simplified) query becomes:
鉴于搜索约会在09:00到13:00之间重叠,Shahkalpesh的(简化)查询变为:
SELECT * FROM Appointments
WHERE (timeStart >= '09:00' OR timeEnd <= '13:00')
This will return all six rows of data. However, only rows 1, 2, 3 overlap the time period 09:00 - 13:00. If rows 1, 2, and 3 are the only valid representative appointment values, then Shahkalpesh's query produces the correct answer. However, if the row 4 (which I think is plausibly valid) is permitted, then it should not be returned. Similarly, rows 5 and 6 - if present - should not be returned. [Actually, assuming timeStart <= timeEnd for all rows in the table (and there are no NULL values to mess things up), we can see that Shahkalpesh's query will return ANY row of data for the 09:00-13:00 query because either the start time of the row is greater 09:00 or the end time is less than 13:00 or both. This is tantamount to writing 1 = 1 or any other tautology in the WHERE clause.]
这将返回所有六行数据。但是,只有第1,2,3行与时间段09:00 - 13:00重叠。如果第1行,第2行和第3行是唯一有效的代表约会值,则Shahkalpesh的查询会生成正确的答案。但是,如果允许第4行(我认为合理有效),则不应返回。同样,不应返回第5行和第6行(如果存在)。 [实际上,假设表中所有行的timeStart <= timeEnd(并且没有NULL值可以搞乱),我们可以看到Shahkalpesh的查询将返回09:00-13:00查询的任意数据行,因为行的开始时间大于09:00或结束时间小于13:00或两者。这相当于在WHERE子句中写1 = 1或任何其他重言式。
If we consider ShaneD's query (as simplified):
如果我们考虑ShaneD的查询(简化):
SELECT * FROM Appointments
WHERE timeStart <= '13:00' AND timeEnd >= '09:00'
we see that it also selects rows 1, 2, and 3, but it rejects rows 4 (because timeStart > '13:00'), 5 (because timeEnd < '09:00') and 6 (because timeStart > '13:00'). This expression is an archetypal example of how to select rows which 'overlap', counting 'meets' and 'met by' (see "Allen's Interval Algebra", for instance) as overlapping. Changing '>=' and '<=' alters the set of intervals counted as overlapping.
我们看到它也选择了行1,2和3,但它拒绝了第4行(因为timeStart> '13:00'),5(因为timeEnd <'09:00')和6(因为timeStart> '13: 00' )。这个表达式是一个典型的例子,说明如何选择“重叠”,计数“满足”和“满足”(例如,参见“Allen's Interval Algebra”)重叠的行。更改'> ='和'<='会更改计为重叠的间隔集。
#3
Thanks Shane, Shahkalpesh, and Jonathan.
谢谢Shane,Shahkalpesh和Jonathan。
I actually overlooked the fact that Shane "swapped" the variables (I was still using timeStart<=$timeStart when it should be timeStart <= $timeEnd). I ran with the modified statement as Jonathan/Shane suggested and it works. As Jonathan did point out, I did obviously missed out some time ranges that I should have tested against.
我实际上忽略了Shane“交换”变量这一事实(我还在使用timeStart <= $ timeStart,它应该是timeStart <= $ timeEnd)。我按照Jonathan / Shane的建议运行修改后的声明并且它有效。正如乔纳森所指出的那样,我确实错过了一些我应该测试的时间范围。
Now with Jonathan's explanation, I now get a better picture of my mistake is and it's helpful.
现在有了Jonathan的解释,我现在可以更好地了解我的错误,这很有帮助。
#4
I think you need an OR.
我想你需要一个OR。
SELECT * FROM appts
WHERE (timeStart >='$timeStart'
OR timeEnd <='$timeEnd')
AND dayappt='$boatdate'
Assuming each record cares about only a specific day. i.e. Boats rented don't run across more than 1 day.
假设每个记录只关心特定的一天。即租用的船只不超过1天。
#1
The correct check would look like this:
正确的检查将如下所示:
SELECT * FROM appts
WHERE timeStart <='$timeEnd'
AND timeEnd >='$timeStart'
AND dayappt='$boatdate'
Other good explanations have been given but I'll go ahead and update it with an alternative explanation of how I visualize this myself. Most people are looking for each possible overlap, considering two time periods, they are trying to think of each combination of start and end that can make an appointment overlap. I think about it as when do two time periods not overlap which for some reason is easier for me.
已经给出了其他很好的解释,但我会继续更新它,并用我自己想象的另一种解释。大多数人都在寻找每个可能的重叠,考虑到两个时间段,他们试图考虑开始和结束的每个组合,这可以使约会重叠。我认为它是因为两个时间段不重叠,由于某种原因对我来说更容易。
Say the time period I am checking for is today, I want to find any time period that does not overlap today. There are really only two scenarios for that, either the time period starts after today (PeriodStart > EndOfToday) or the time period ends before today (PeriodEnd < StartOfToday).
说我正在检查的时间段是今天,我想找到今天不重叠的任何时间段。实际上只有两种情况,即时间段在今天之后开始(PeriodStart> EndOfToday)或时间段在今天之前结束(PeriodEnd
Given that we havea simple test for not overlapping:
(PeriodStart > EndOfToday) OR (PeriodEnd < StartOfToday)
鉴于我们有一个不重叠的简单测试:(PeriodStart> EndOfToday)OR(PeriodEnd
A quick flip around and you have a simple test for overlap:
(PeriodStart <= EndOfToday) AND (PeriodEnd >= StartOfToday)
快速翻转,你有一个简单的重叠测试:(PeriodStart <= EndOfToday)AND(PeriodEnd> = StartOfToday)
-Shane
#2
Shahkalpesh answered the question with:
Shahkalpesh回答了这个问题:
I think you need an OR.
我想你需要一个OR。
SELECT * FROM appts WHERE (timeStart >='$timeStart' OR timeEnd <='$timeEnd') AND dayappt='$boatdate'
I posted a comment that I consider this to be wrong, giving a pair of counter-examples:
我发表了一条评论,我认为这是错误的,给出了一对反例:
This is plain wrong - @ShaneD is correct. For example, this will pick out a booking between 05:00 and 06:00 because the actual end time is less than any of the end times you ask about. It will also pick up rentals from 18:00 onwards, for the equivalent reason.
这是完全错误的 - @ShaneD是正确的。例如,这将在05:00和06:00之间选择预订,因为实际结束时间少于您询问的任何结束时间。由于同等原因,它还将从18:00开始接收租金。
In a response to my comment, Shahkalpesh requested:
在回应我的评论时,Shahkalpesh要求:
Could you post a separate reply with data & input parameters with expected output?
您是否可以使用预期输出发布带有数据和输入参数的单独回复?
Fair enough - yes. Slightly edited, the question says:
足够公平 - 是的。略有编辑,问题说:
The logic is that a boat rental can be reserved
逻辑是可以预订船只租赁
- from 7am until 1pm, or
- from 9am until 1pm, or
- from 9am until 5pm.
从早上7点到下午1点,或
从上午9点到下午1点,或
从上午9点到下午5点。
If there is an appointment within that range, it should return appointments but it has proven to be inconsistent. If I pick 9am until 1pm, ...
如果在该范围内有预约,则应该返回预约,但事实证明它是不一致的。如果我选择上午9点到下午1点,...
Enough background. We can ignore the date of the appointments, and just consider the times. I'm assuming that there is an easy way to limit the times recorded to hh:mm format; not all DBMS actually provide that, but the extension to handle hh:mm:ss is trivial.
足够的背景。我们可以忽略约会的日期,只考虑时间。我假设有一种简单的方法可以将记录的时间限制为hh:mm格式;并非所有DBMS实际上都提供了这一点,但处理hh:mm:ss的扩展是微不足道的。
Appointments
Row timeStart timeEnd Note
1 07:00 13:00 First valid range
2 09:00 13:00 Second valid range
3 09:00 17:00 Third valid range
4 14:00 17:00 First plausibly valid range
5 05:00 06:00 First probably invalid range
6 18:00 22:30 Second probably invalid range
Given a search for appointments overlapping the range 09:00 - 13:00, Shahkalpesh's (simplified) query becomes:
鉴于搜索约会在09:00到13:00之间重叠,Shahkalpesh的(简化)查询变为:
SELECT * FROM Appointments
WHERE (timeStart >= '09:00' OR timeEnd <= '13:00')
This will return all six rows of data. However, only rows 1, 2, 3 overlap the time period 09:00 - 13:00. If rows 1, 2, and 3 are the only valid representative appointment values, then Shahkalpesh's query produces the correct answer. However, if the row 4 (which I think is plausibly valid) is permitted, then it should not be returned. Similarly, rows 5 and 6 - if present - should not be returned. [Actually, assuming timeStart <= timeEnd for all rows in the table (and there are no NULL values to mess things up), we can see that Shahkalpesh's query will return ANY row of data for the 09:00-13:00 query because either the start time of the row is greater 09:00 or the end time is less than 13:00 or both. This is tantamount to writing 1 = 1 or any other tautology in the WHERE clause.]
这将返回所有六行数据。但是,只有第1,2,3行与时间段09:00 - 13:00重叠。如果第1行,第2行和第3行是唯一有效的代表约会值,则Shahkalpesh的查询会生成正确的答案。但是,如果允许第4行(我认为合理有效),则不应返回。同样,不应返回第5行和第6行(如果存在)。 [实际上,假设表中所有行的timeStart <= timeEnd(并且没有NULL值可以搞乱),我们可以看到Shahkalpesh的查询将返回09:00-13:00查询的任意数据行,因为行的开始时间大于09:00或结束时间小于13:00或两者。这相当于在WHERE子句中写1 = 1或任何其他重言式。
If we consider ShaneD's query (as simplified):
如果我们考虑ShaneD的查询(简化):
SELECT * FROM Appointments
WHERE timeStart <= '13:00' AND timeEnd >= '09:00'
we see that it also selects rows 1, 2, and 3, but it rejects rows 4 (because timeStart > '13:00'), 5 (because timeEnd < '09:00') and 6 (because timeStart > '13:00'). This expression is an archetypal example of how to select rows which 'overlap', counting 'meets' and 'met by' (see "Allen's Interval Algebra", for instance) as overlapping. Changing '>=' and '<=' alters the set of intervals counted as overlapping.
我们看到它也选择了行1,2和3,但它拒绝了第4行(因为timeStart> '13:00'),5(因为timeEnd <'09:00')和6(因为timeStart> '13: 00' )。这个表达式是一个典型的例子,说明如何选择“重叠”,计数“满足”和“满足”(例如,参见“Allen's Interval Algebra”)重叠的行。更改'> ='和'<='会更改计为重叠的间隔集。
#3
Thanks Shane, Shahkalpesh, and Jonathan.
谢谢Shane,Shahkalpesh和Jonathan。
I actually overlooked the fact that Shane "swapped" the variables (I was still using timeStart<=$timeStart when it should be timeStart <= $timeEnd). I ran with the modified statement as Jonathan/Shane suggested and it works. As Jonathan did point out, I did obviously missed out some time ranges that I should have tested against.
我实际上忽略了Shane“交换”变量这一事实(我还在使用timeStart <= $ timeStart,它应该是timeStart <= $ timeEnd)。我按照Jonathan / Shane的建议运行修改后的声明并且它有效。正如乔纳森所指出的那样,我确实错过了一些我应该测试的时间范围。
Now with Jonathan's explanation, I now get a better picture of my mistake is and it's helpful.
现在有了Jonathan的解释,我现在可以更好地了解我的错误,这很有帮助。
#4
I think you need an OR.
我想你需要一个OR。
SELECT * FROM appts
WHERE (timeStart >='$timeStart'
OR timeEnd <='$timeEnd')
AND dayappt='$boatdate'
Assuming each record cares about only a specific day. i.e. Boats rented don't run across more than 1 day.
假设每个记录只关心特定的一天。即租用的船只不超过1天。