How to calculate time difference between two non-consecutive records using mysql

Question

0.00/5 (No votes)

See more:

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:

SQL

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;

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.

Posted 2-Feb-19 5:36am

Tino Fourie

Updated 8-Feb-19 19:56pm

Add a Solution

Comments

Maciej Los 3-Feb-19 16:46pm

Can you provide a "short version" and a small-piece-of-data in the following format: appID, appStartTime, appEndTime, etc.? What field is primary key?

Tino Fourie 7-Feb-19 5:13am

Hi Maciej, thanks for your comment.

The appID is the PK however, it will be pointless to even consider the appID because I can have sequential appID's but non-sequential time periods.

appID = INT(11) AUTO INCREMENT
appStartTime = DateTime (2019-01-01 10:00:00)
appEndTime = DateTime (2019-01-01 10:15:00)

I am indeed looking for times where a resources is not booked, however the user will not be able to see if a resource is booked or not because the type of booking will determine what the time period will be between the appStartTime and appEndTime.

Richard Deeming 4-Feb-19 10:54am

That doesn't sound right to me. If I book a 2 hour appointment from 3PM today, and the room is only available for 2 hours from 9AM, then I wouldn't consider that an acceptable alternative.

Are you looking for resources which don't have an overlapping booking instead?

Maciej Los 4-Feb-19 16:44pm

Seems, OP is looking for gaps and islands in date and time...

Maciej Los 4-Feb-19 16:45pm

Take a look here:
mysql - identify gaps and islands - Database Administrators Stack Exchange[^]
SQL Server Window Functions Gaps and Islands Problem[^]
The SQL of Gaps and Islands in Sequences - Simple Talk[^]

2 solutions

Solution 1

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[^]

Posted 4-Feb-19 21:28pm

Maciej Los

Comments

Tino Fourie 7-Feb-19 5:19am

My apologies for the late reply - I was waiting 2 days for replies myself.

I was advised to look into Analytic Functions - LEAD and LAG, however it was a little over my head (and still is) the basic sample code I tried did not work at all and MySQL Workbench does not provide clear debug messages.

Thank you for the , I will certainly look into that.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tino Fourie · Accepted Answer · 2019-02-08T19:56:00

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.

VB

'(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

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.