在没有日历表的情况下搜索给定范围内的可用日期 [英] Search for available dates within a given range without calender table

问题描述

我正在使用带导游的预订系统. 并且在任何人抱怨这个问题是这里的重复之前:

i am on a reservation system for guided tours. And before anyone complains about this question to be a dublicate of this one here: Help with SQL query to find next available date for a reservation system i want to point out that I don't want to use a calender table.

一些基本信息:

预订表结构:

Reservation table structur:

CREATE TABLE IF NOT EXISTS `reservations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guider_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `duration` int(11) NOT NULL,
  PRIMARY KEY (`id`),
) ;

userStartDateTime =用户在菜单中选择的开始日期和时间 保留系统userEndDateTime =用户的开始日期和时间 在预订系统中选择+持续时间

userStartDateTime = the start date and time the user choose in the reservation system userEndDateTime = the start date and time the user choose in the reservation system + the duration

游览的最短持续时间为15分钟.

Minimum duration for a tour is 15 minutes.

这是交易: 客户来到预订系统.他们会选择日期和时间，持续时间和范围，以防所选的日期/时间不再可用.

Here's the deal: Customers come to the reservation system. They choose a date and a time, a duration and a range in case the choosen date/time is not available anymore.

我们有不同的指南，但总是相同的游览，只是他们所花费的时间和参观地点有所不同(此时无关紧要).因此，保留可以使自己跃升至一定水平.每个导游每天24/7全天候可用. 但是每次游览后都有5分钟的缓冲时间，以避免延误.

We have different guides but it's always the same tour only differed by the duration they take and the places to see (which does not matter at this point). So the reservations can overleap themselves up to a certain level. Every guider is available at everytime of the day, 24/7. But there is a buffer of 5 minutes after every tour to avoid delays.

我通过将游览的持续时间添加到开始时间来生成结束日期. 我在预订表中搜索带有BETWEEN where子句的所有已采用指南，如下所示:

I generate the end date by adding the duration of a tour to the start time. I searching in the reservations table for all taken guides with a BETWEEN where clause like this:

SELECT guider_id FROM reservations as r
WHERE 
 (((r.startDateTime - interval 5 minute) BETWEEN userStartDateTime AND userEndDateTime)) OR
 ((r.startDateTime + interval r.duration minute + interval 5 minute) BETWEEN userStartDateTime AND userEndDateTime))

然后，我在指导者表上使用"NOT IN"来找到可用的指导者(如前所述，每个指导者在每天的每个时间都可用).如果我找到一个:是的，预订成功.如果没有...我需要找到一个可用的日期/时间.

Afterwards I use "NOT IN" on the guiders table to find available guiders (like mentioned every guider is available at every time of day). If i find one: yeah, reservation successfull. If not...I need to find a date/time where one is available.

那是我被困住的地方. 我不想创建一个表来存储每个可用的日期时间组合，因为那样会浪费内存，并且意味着很多工作和性能.因为一天中的每个时间都可以选择.这意味着，每次预订时，我都需要重新安排当天的整个桌子.该表将已经一天和一年(假设360天)的518400已有1440个条目.要在接下来的十年中填充该表...，您可以自己计算.甚至不值得考虑.

That is where i am stucked. I do not want to create a table where every available date time combination is stored because that is hell of a memory waste and means hell of a lot work and performance. Because every time of the day could be choosen. Which means, with every reservation i need to re-arrange the whole table for that day. That table would hold already 1440 entries for a day and for a year (assuming 360 days) 518400. To fill that table for the upcoming ten years...you can calculate yourself. That's not even worth thinking about it.

所以我现在要做的是:我需要在userStartDateTime范围内找到一个新的日期/时间.首先是前进(首选)，如果没有找到任何免费的向导，则为后退.

So what i need to do now is: i need to find a new date/time within the range from the userStartDateTime. First forward (preferred) and if no free guider has been found by that backward from that.

那我该怎么办?

我对此没有任何想法. 只是将范围添加到我计算出的结束日期是行不通的，因为它会吸引很多能够在其中开始和结束旅程的向导.

I just have no working idea about that. Just adding the range to my calculated end date would not work, 'cause it would catch to much guiders which have a tour starting and finishing within it.

问候 func0der

Greetings func0der

推荐答案

如何创建一个名为scheduleTour的新表(例如).然后，只要需要添加新的导览或删除导览，只需更新表即可.因此，从本质上讲，当首次创建表时，它将完全为空.

What about creating a new table called scheduledTour (just for example). Then just UPDATE the table whenever you need to add a new guided tour, or remove one. So, essentially, when the table would first be made it would be completely empty.

ScheduledTour表将具有以下信息:tour_id，guider_id，customer_id，startDateTime，endDateTime.

The scheduledTour table would have information like: tour_id, guider_id, customer_id, startDateTime, endDateTime.

tour_id只是一个自动递增的INT值，可用于保持条目唯一. guider_id是您在问题中提到的那个，它将引用您已经拥有的表中分配的指导者，并且与customer_id相同(如果您有客户表.如果没有，则可以替换票号，姓名等) ).

The tour_id would just be an auto-increment INT value that you can use to keep entries unique. The guider_id is the one you mentioned in your question, it would reference the assigned guider from the table you already have, and the same with customer_id (if you have a customer table. If not you can replace w/ ticket number, name, etc.).

通过这种方式，计划的巡回行程数没有限制，您可以轻松地创建一个添加/删除页面.您可以拉出特定向导的导览，所有导览或带有特定开始和结束日期/时间的导览.

This way there is no limit to how few or many scheduled tours, and you can easily create an add/drop page. You can pull the tours for specific guides, all of the tours, or tours w/ specific start and end dates/times.

此外，如果所有游览时间均为5分钟.缓冲区，然后在创建游览时将其添加到开始时间和结束时间.

Also, if all tours have a 5min. buffer, then just add that to the start and end times when you create the tour.

例如，在您输入日期之后的"UPDATE"语句中:

For example, in your "UPDATE" statement right after the date you are entering:

... DATE_ADD([startDateTime], INTERVAL 5 MINUTE) ...

希望这会有所帮助！

• "DATE_ADD()" 参考

这篇关于在没有日历表的情况下搜索给定范围内的可用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！