查找具有特定DateTime范围的结果 [英] Find result with specific DateTime range

问题描述

我的桌子

Hi,
My table

----------------------------------------------------------
Reservation ID | StartDateTime       | EndDateTime
00001          | 2013-06-01 01:00:00 | 2013-06-01 03:00:00
00002          | 2013-06-01 02:00:00 | 2013-06-01 05:00:00
00003          | 2013-06-01 03:00:00 | 2013-06-01 06:00:00
00004          | 2013-06-04 06:00:00 | 2013-06-01 07:00:00
00004          | 2013-06-04 09:00:00 | 2013-06-01 11:00:00

-

-

-



我试着从2013-06-01 02:00:00到2013-06-01 03:00:00找到记录。使用以下内容

-
-
-

I try to find record from 2013-06-01 02:00:00 to 2013-06-01 03:00:00. With the following

SELECT *
FROM reservation
WHERE((startDateTime BETWEEN '2013-06-01 02:00:00' AND '2013-06-01 03:00:00') OR
(endDateTime BETWEEN '2013-06-01 02:00:00' AND '2013-06-01 03:00:00') OR
('2013-06-01 02:00:00' BETWEEN startDateTime AND endDateTime) OR
('2013-06-01 03:00:00' BETWEEN startDateTime AND endDateTime));

结果

The result

----------------------------------------------------------
Reservation ID | StartDateTime       | EndDateTime
00001          | 2013-06-01 01:00:00 | 2013-06-01 03:00:00
00002          | 2013-06-01 02:00:00 | 2013-06-01 05:00:00
00003          | 2013-06-01 03:00:00 | 2013-06-01 06:00:00 <--- wrong

我不想要03:00:00因为它超出了我的时间范围。如何省略03:00:00。很难解释。我想要02:00:00到03:00:00的预订ID但是它给了我额外的一个。虽然结果是正确的，但作为功能，但....叹息。 idk。

I don't want the 03:00:00 because it's out of my time range. How to omit the 03:00:00. It's hard to explain. I want reservation ID of 02:00:00 to 03:00:00 but it gave me extra one. Although the result is correct and work as function but ....sigh. idk.

推荐答案

这很简单。

It's simple.

SELECT *
FROM reservation
WHERE (startDateTime >='2013-06-01 02:00:00') AND (endDateTime <='2013-06-01 03:00:00'

以上查询返回0条记录。

Above query returns 0 records.

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00') AND (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

上面的查询返回0条记录。在两个条件之间用 OR 运算符进行测试：

Above query returns 0 records.Test it with OR operator between two conditions:

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00') OR (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

有什么区别？

What's the difference?

SELECT *
FROM reservation
WHERE (startDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

以上查询返回 ID 00002 。

Above query returns ID 00002.

SELECT *
FROM reservation
WHERE (endDateTime BETWEEN '2013-06-01 02:00:00'  AND '2013-06-01 03:00:00')

以上查询返回 ID 00001 。



请阅读 MySQL逻辑运算符 [ ^ ]。

最终答案。

The ultimate answer.

Select *
From reservation
Where '2013-06-01 02:00:00' < endDateTime AND '2013-06-01 03:00:00' > startDateTime

谢谢大家!!!

Thank you everyone!!!

这篇关于查找具有特定DateTime范围的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！