How do you find overlapping bookings in SQL?
Two bookings overlap when the existing booking starts before the proposed booking ends, and the existing booking ends after the proposed booking starts. In SQL, the core rule is existing_start < proposed_end AND existing_end > proposed_start. Check the same resource and only the booking states that reserve capacity.
This tutorial applies that rule to a fictional meeting-room application in OriginChainDB. You will create a small dataset, find a conflict, explain adjacent bookings, handle an edit, and build a test matrix. The result is an availability checker. A separate concurrency design is still required to guarantee that simultaneous requests cannot double-book a room.
The same pattern is useful for equipment loans, studio appointments and vehicle reservations. Start with a resource that has capacity one. Shared-capacity events and recurring calendars require additional rules; they are not just this example with a different table name.
The problem: a calendar that looks free can still be wrong
A receptionist wants to reserve room Cedar from 10:30 to 11:30. The calendar contains a booking from 10:00 to 11:00 and another from 11:30 to noon. A query that only looks for bookings starting between 10:30 and 11:30 misses the first reservation. Its start is earlier, but it still occupies the room during the requested interval.
This mistake appears in booking products because the visible calendar grid hides the underlying question. You are not searching for start times. You are asking whether two occupied intervals share any time. Write that question down before choosing a query.
Use a running OriginChainDB instance and a demo account with permission to create and query tables. Open the SQL Workbench and use a fresh demo namespace. All names and records below are fictional. Run the statements one at a time so each result is easy to inspect. The SQL uses the documented create, insert, filter, order and update operations. See the OriginChainDB SQL reference.
1. Choose one clear time convention
For a compact, reproducible example, store minutes after midnight on one fictional service day: 600 means 10:00 and 660 means 11:00. This makes boundary cases readable without a date-conversion library. These values are not production timestamps.
Use half-open intervals: the start is included and the end is excluded. A reservation written as [600,660) occupies the room at 10:00 but releases it at 11:00. A second booking may start at 660 without overlapping. If cleaning requires fifteen minutes, model that buffer explicitly rather than quietly changing the meaning of the comparison operators.
In a production system, resolve the user’s local date, time and time zone into a consistent instant representation before checking conflicts. Reject nonexistent local times and ask the user to resolve ambiguous ones. Keep the intended display time zone with the booking. A recurring event also needs its recurrence rule; one pair of timestamps does not describe every future occurrence.
2. Create a small dataset you can reason about
CREATE SCHEMA booking_demo_20261007;
CREATE TABLE booking_demo_20261007.bookings (
id TEXT PRIMARY KEY,
room_id TEXT,
start_min BIGINT,
end_min BIGINT,
status TEXT
);
INSERT INTO booking_demo_20261007.bookings
(id, room_id, start_min, end_min, status)
VALUES
('b1', 'cedar', 600, 660, 'confirmed'),
('b2', 'cedar', 690, 720, 'confirmed'),
('b3', 'cedar', 630, 660, 'cancelled'),
('b4', 'maple', 630, 690, 'confirmed');
SELECT id, room_id, start_min, end_min, status
FROM booking_demo_20261007.bookings
ORDER BY id;Expect four records. Cedar has two active reservations and one cancelled reservation. Maple is a different room, so its reservation does not prevent using Cedar. This separation is deliberate: it proves that resource identity and booking status are part of the rule, not optional display filters.
Before accepting an application request, require a known room, a permitted status, and start_min < end_min. A zero-length interval is not a useful reservation in this example. Reject reversed endpoints rather than silently swapping them. These checks belong in the application contract even when a database constraint also enforces part of the contract.
3. Find the conflict for 10:30–11:30
SELECT id, start_min, end_min
FROM booking_demo_20261007.bookings
WHERE room_id = 'cedar'
AND status = 'confirmed'
AND start_min < 690
AND end_min > 630
ORDER BY start_min, id;The intended result is b1. It starts at 600, before the proposed end of 690, and finishes at 660, after the proposed start of 630. Both comparisons are true, so there is shared occupied time.
Booking b2 starts exactly at the proposed end. The strict comparison 690 < 690 is false, so the two intervals are adjacent rather than overlapping. Booking b3 is cancelled, and b4 belongs to Maple. Neither should appear.
Return the conflicting booking IDs and permitted display details to the user. “Cedar is occupied until 11:00” is more useful than a generic validation error. Be careful with private meeting titles: the existence of a conflict does not necessarily authorize showing its attendees or purpose.
4. Prove the boundary cases before building the screen
Use this table as an acceptance test against the original dataset. The proposed room is Cedar unless another room is stated.
| Proposed interval | Expected conflicts | Reason |
|---|---|---|
09:30–10:00 [570,600) | None | Ends when b1 starts |
10:00–11:00 [600,660) | b1 | Exactly the same occupied interval |
10:30–11:30 [630,690) | b1 | Partial overlap |
11:00–11:30 [660,690) | None | Fits between the active bookings |
09:00–12:30 [540,750) | b1 and b2 | Contains both active intervals |
10:15–10:45 [615,645) | b1 | Lies entirely inside b1 |
| Maple, 10:30–11:30 | b4 | Resource-specific conflict |
The containment cases matter. A test suite made only of partial overlaps can pass while a start-time-only implementation remains wrong. Also test an unknown resource, a missing endpoint and a cancelled request. Decide whether each should be rejected or treated as non-reserving before users discover the ambiguity.
5. Recheck edits without conflicting with the same booking
If a user edits b1, the conflict search must exclude b1’s current row. Otherwise an unchanged booking conflicts with itself.
SELECT id, start_min, end_min
FROM booking_demo_20261007.bookings
WHERE room_id = 'cedar'
AND status = 'confirmed'
AND id != 'b1'
AND start_min < 700
AND end_min > 660
ORDER BY start_min, id;This proposed edit moves b1 to 11:00–11:40. The intended conflict is b2, which begins at 11:30. Excluding the edited row must not exclude other rows for that room.
For comparison, an edit to 11:00–11:30 has no conflict in this fixture. After an accepted edit, read the stored record again and refresh availability. In application code, pass values through the supported parameter interface rather than concatenating user input into SQL. Keep the stable booking ID unchanged when its display details change.
6. Understand the race between checking and saving
Two users can ask for the same free interval at the same time. Both availability queries may return no rows. If both applications then insert a reservation, both can succeed unless the write path enforces an appropriate concurrency rule.
A successful SELECT is evidence about the state it read; it is not a reservation. Likewise, wrapping a check and insert in a transaction does not by itself prove that every overlapping-interval race is prevented. Isolation and conflict behavior must be established for the deployed interface. Review the transaction documentation before choosing a design.
One application-level starting point is to serialize booking commands per resource through a durable coordinator, then recheck availability inside the controlled write path. Another model divides a calendar into uniquely claimable slots, but that changes the representation and must handle multi-slot reservations atomically. Choose a design you can test under contention, failures and retries. A process-local lock alone does not protect requests handled by different application servers.
Give each reservation request a stable application request identity. A lost response should not cause the client to create a second unrelated reservation. Store enough state to tell the caller whether its original command completed. Avoid promising exactly-once behavior unless the complete path supports it.
7. Treat cancellation, buffers and capacity as explicit policies
For a simple cancellation demonstration:
UPDATE booking_demo_20261007.bookings
SET status = 'cancelled'
WHERE id = 'b1';Repeat the 10:30–11:30 query. The intended result is now empty because b1 no longer reserves Cedar, and b2 is still adjacent. Read b1 separately to confirm that the row still exists with the changed status. Cancellation and deletion are different operations.
If cleaning blocks a room for fifteen minutes after a meeting, choose whether to store a separate blocked interval or calculate the effective occupied end. Explain that policy on the booking screen. For a resource with capacity greater than one, you need occupancy counts across the requested interval; the existence of one overlap is no longer enough to reject the request.
Only consider performance after the correctness tests pass. Measure with realistic booking history, room distribution and query frequency. Inspect the deployed query plan before assuming an index will accelerate both inequalities. Retain a small readable fixture even if you later add a much larger performance dataset.
Frequently asked questions
Does touching an endpoint count as overlap?
Not under the half-open convention used here. One booking may end when the next begins. If handover time is required, add a documented buffer to the occupied interval.
Can I use BETWEEN instead?
A single BETWEEN test on the existing start time misses reservations that begin earlier and continue into the requested interval. The two-comparison rule handles partial overlap and containment.
Does this tutorial prevent double booking in production?
It demonstrates conflict detection. Preventing double booking also requires a concurrency-safe write path, input validation, retry handling and testing across multiple application workers.
Should the application delete cancelled bookings?
That depends on its retention policy. Keeping a cancelled state can explain an earlier decision, while deletion has different operational consequences. Make the choice explicit and apply it consistently across all readers.
Your next step
Build the four-row fixture in an isolated OriginChainDB workspace. Explain every row returned by the conflict query, then run the boundary matrix and the cancellation test. Once the read behavior is correct, test the reservation write path with simultaneous clients. A trustworthy booking experience needs both a clear overlap rule and a clear answer to who can claim the room.
Continue with the SQL reference, schema guide and transaction reference.