如何用BETWEEN子句过滤双DateTime字段？大厅预订系统空闲查询

Hey there! Let's walk through building this hall booking availability checker step by step. I'll cover how to detect conflicting bookings, write the SQL to find free halls, and clarify how to use the BETWEEN clause with datetime fields.

首先：理解时间冲突的核心逻辑 #

Before jumping into code, we need to define when a user's requested time slot conflicts with an existing booking. Two time ranges ([ExistingFrom, ExistingTo] and [QueryFrom, QueryTo]) overlap if neither range ends before the other starts.

In simpler terms, a conflict happens if:

NOT (ExistingTo <= QueryFrom OR ExistingFrom >= QueryTo)

This single condition covers all overlap scenarios:

• Query slot fully contains an existing booking
• Query slot is fully contained within an existing booking
• Query slot overlaps the start of an existing booking
• Query slot overlaps the end of an existing booking

SQL查询空闲大厅 #

Assuming you have a Halls table that lists all available halls (since the Orders table only tracks booked halls), here's the query to find halls free for your requested time:

-- Replace @QueryFromDateTime and @QueryToDateTime with user input values
SELECT h.HallID
FROM Halls h
LEFT JOIN Orders o 
  ON h.HallID = o.HallID
  -- Join only on bookings that conflict with the query slot
  AND NOT (o.ToDateTime <= @QueryFromDateTime OR o.FromDateTime >= @QueryToDateTime)
-- Keep only halls with NO conflicting bookings
GROUP BY h.HallID
HAVING COUNT(o.HallID) = 0;

If you don't have a dedicated Halls table, you can first get all unique halls from the Orders table (plus any unbooked halls you need to include manually):

WITH AllHalls AS (
  -- Get all halls that have been booked before
  SELECT DISTINCT HallID FROM Orders
  -- Add any unbooked halls here, e.g.:
  -- UNION SELECT 4 FROM DUAL
)
SELECT ah.HallID
FROM AllHalls ah
LEFT JOIN Orders o 
  ON ah.HallID = o.HallID
  AND NOT (o.ToDateTime <= @QueryFromDateTime OR o.FromDateTime >= @QueryToDateTime)
GROUP BY ah.HallID
HAVING COUNT(o.HallID) = 0;

使用BETWEEN子句过滤DateTime字段 #

The BETWEEN clause works with datetime fields as a closed interval (it includes both the start and end values). For example, to find all bookings that start between two times:

SELECT * FROM Orders
WHERE FromDateTime BETWEEN '2018-01-01 00:00:00' AND '2018-01-01 12:00:00';

⚠️ Important note: BETWEEN alone isn't enough to check for overlapping time slots. It only covers cases where one datetime falls within a range, not full range overlaps. For example, if an existing booking is [03:00, 05:00] and the query is [02:00, 04:00], FromDateTime BETWEEN @QueryFrom AND @QueryTo won't catch the conflict—but our earlier conflict logic will.

测试场景验证 #

Here are key test cases to validate your system:

• Full overlap: Query [2018-01-01 04:00, 04:30] → Hall 1 should be excluded (conflicts with its 03:00-05:00 booking)
• Partial overlap (end): Query [2018-01-01 04:30, 06:00] → Hall 1 is excluded, Halls 2 and 3 are available
• Full coverage: Query [2018-01-01 02:00, 16:00] → All halls are conflicting, so no results
• Idle window: Query [2018-01-01 05:00, 06:30] → All halls are available (Hall 1 ends at 05:00, Hall 2 starts at 06:30)
• Unbooked hall: If you add a Hall 4 with no orders, it should appear in results for any query

内容的提问来源于stack exchange，提问作者Harsha W