如何使用mysql计算两个非连续记录之间的时差 [英] How to calculate time difference between two non-consecutive records using mysql

问题描述

我在WinForms应用程序（vb.net）中使用调度程序（日历）并将约会（startDateTime& endDateTime）保存到MySql数据库。



期间在预约的过程中，我需要检查特定日期某段时间（分钟）是否有某种资源（例如：一间房间）。



我在这里寻找适合我的场景的可能解决方案，但是，所有解决方案都适用于在特定时间段内搜索条目。



我存储StartTime和EndTime预约到数据库以及该预约所需的任何资源。



我可以有多个资源（例如：多个房间）并希望如果在该时间段内默认（或选定的房间）资源不可用，则能够找到具有未分配时间段的房间。



一段时间可以从10分钟到2小时不等，但它总是一个固定的时间段r预约类型和资源，例如：预约：董事会会议



资源：董事会会议室



开始时间：02/02/2019 09:00:00



结束时间：02/02/2019 10:00:00



任何帮助都将不胜感激。



我尝试过：



代码我在摆弄：

I use a scheduler (calendar) in a WinForms application (vb.net) and save appointments (startDateTime & endDateTime) to a MySql DB.

During the process of making an appointment, I need to check if a certain resource (e.g: a Room) is available for a certain period of time (minutes) on a particular day.

I have looked here for possible solutions that fits my scenario, however, all solutions pertain to searching for entries within a particular time period.

I store the StartTime and EndTime for an appointment to the DB as well as any required resources for that appointment.

I can have multiple resources (e.g: more than one Room) and would like to be able to find a room with an un-allocated time period should the default (or selected room) resources not be available for that time period.

A time period can vary from 10 min up to 2 hours, but it will always be a set period for the type of appointment and resource, e.g: Appointment: Board Meeting

Resource: Board Room

Start Time: 02/02/2019 09:00:00

End Time: 02/02/2019 10:00:00

Any help would be appreciated.

What I have tried:

Code I am fiddling with:

SELECT (TIME(b.appStartTime) - TIME(a.appEndTime)) as timedifference, 
a.appID, a.appOwnerKey, a.appEndTime, b.appStartTime, a.appSubject, 
a.appDescription, a.appToolTip, a.appCategory, a.appImg, a.appPatWaiting

FROM cusAppointments a 
INNER JOIN cusAppointments b ON b.appID = (a.appID + 1)
WHERE (DATE(a.appStartTime) = DATE(NOW()) AND
       DATE(b.appEndTime) = DATE(NOW()))
AND (TIME(b.appStartTime) - TIME(a.appEndTime)) >= 15000
AND a.appOwnerKey = 'Admin .'
ORDER BY a.appStartTime ASC;

这是行不通的，因为在我看来我应该使用在StartTime上排序的同一个表的两个记录集然后减去RecordSet的r的EndTime来自RecordSet B的rowid + 1的StartTime的owid。

This is not working because in my mind I should be working with two record sets of the same table ordered on StartTime then substract EndTime of RecordSet A's rowid from the StartTime of RecordSet B's rowid + 1.

推荐答案

我不确定你想要实现什么，因为你没有回答评论，但似乎你正在寻找能找到差距和岛屿的算法。请参考： mysql - 识别差距和孤岛 - 数据库管理员堆栈交换 [< a href =https://dba.stackexchange.com/questions/112178/identify-gaps-and-islandstarget =_ blanktitle =New Window> ^ ]

I'm not sure what you want to achieve, because you don't answer to comments, but seems that you're looking for algorithm which will find gaps and islands. Please, refer this: mysql - identify gaps and islands - Database Administrators Stack Exchange[^]

经过一番考虑并意识到有一些事情我没有考虑到，我决定宁愿把处理交给客户。



我没有考虑的事情：

1. StartOfDay时间（办公时间开始）

2. EndOfDay时间（办公时间结束）

3.获得首次预订 - 检查首次预订和StartOfDay之间未分配的时间段

4.获取最后预订 - 检查上次预订之间未分配的时间段和EndOfDay



我使用了一个简单的If..Else语句块来实现逻辑，它只需要11个代码行检查一切。

首先，我检查首次预订，然后查看我是否有第一次预订的StartTime和StartOfDay时间之间的开放时间段。



第二张支票是最后一次预订，看看上次预订的结束时间和结束时间之间是否有空缺。



第三部分是处理第一次和最后一次预订之间的所有预订。

After some consideration and realising that there were a few things I did not take into account, I've decided to rather take the processing to the client.

Things I did not take into account:
1. StartOfDay Time (Start of Office Hours)
2. EndOfDay Time (End of Office Hours)
3. Get First Booking - to check for an un-allocated time period between First Booking and StartOfDay
4. Get Last Booking - to check for an un-allocated time period between Last Booking and EndOfDay

I've used a simple If..Else statement block to implement the logic and it takes only 11 code lines to check for everything.
First I check for First Booking and then check to see if I have an open time period between the First Booking's StartTime and StartOfDay time.

The Second check is for the Last Booking to see if there is an opening between the Last Booking's EndTime and EndOfDay time.

The third part is to process all bookings between the First and Last Booking.

'(1) Check if there is a TimeSlot available between the first returned record and the Start Of Day
                    '   - Check if we have the first record (j=0)
                    '   - Check if myEndTime is less or equal to dbStartTime of first record
                    '   - Check if myStartTime is greater or equal to StartOfDay
                    '   - Check the Time Difference between dbStartTime of first record and StartOfDay and check if
                    '       myTimeSlot is less or equal to that time

                    If j = 0 AndAlso myEndTime <= dbStartTime AndAlso
                        myStartTime >= StartOfDay AndAlso (dbStartTime - StartOfDay) >= myTimeSlot Then
                        txtResults.AppendText("Found Time Slots" & vbCrLf)
                        'Check for LAST record and do similar as for FIRST record
                    ElseIf j = clReadFromDB.tmsData.Rows.Count - 1 AndAlso myEndTime <= EndOfDay AndAlso
                        (EndOfDay - dbEndTime) >= myTimeSlot Then
                        txtResults.AppendText("Found Time Slots" & vbCrLf)
                        'Check for a time slot inbetween the FIRST and LAST record
                        '   - We don't have to bother with checking for StartOfDay or EndOfDay
                        '   - Check if myStartTime >= dbEndTime of the Current Record (j)
                        '   - Check if myEndTime <= dbStartTime of the Next Record (j+1)
                        '   - Check if myTimeSlot is <= to the time difference between dbDStartTime of Current Record
                        '     and dbStartTime of the Next Record
                    ElseIf myStartTime >= clReadFromDB.tmsData.Rows(j).Item(2) AndAlso
                        myEndTime <= clReadFromDB.tmsData.Rows(j + 1).Item(1) AndAlso
                        (clReadFromDB.tmsData.Rows(j + 1).Item(1) - clReadFromDB.tmsData.Rows(j).Item(2)) >= myTimeSlot Then
                        txtResults.AppendText("Found Time Slots" & vbCrLf)
                        lblStatus.Text = Convert.ToString(clReadFromDB.tmsData.Rows(j + 1).Item(1) - clReadFromDB.tmsData.Rows(j).Item(2))
                    Else
                        lblStatus.Text = "Time Slot not found"
                        txtResults.AppendText("Time Slot Not Found" & vbCrLf)
                    End If

我将使用上面的代码作为基础逻辑，因为它贯穿了DB上的所有预订。它还使用First Booking作为初始支票，而不是用户为新预订选择的日历时间。



感谢那些试图提供帮助的人，即使你的建议并没有让我更接近MySQL的解决方案，但它确实拓宽了我对MySQL的认识。

I will use the above code as the foundation logic because this runs through all the bookings on the DB. It also uses the First Booking as the initial check instead of the Calendar Time the user selected for the new Booking.

Thank you to those who tried to assist, even though your suggestions did not bring me closer to a MySQL solution, it did however broaden my knowledge of MySQL.

这篇关于如何使用mysql计算两个非连续记录之间的时差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！