MySQL function to calculate elapsed working time
I wrote this function to cater for a specific requirement and I don't know if there are better ways of doing it but this saved tremendous amount of time and might have real time application elsewhere.
Problem Statement:¶
Find out the age of an incident in working minutes, given the following:
- Time and Date of when an incident was logged
- Time and date of when the same incident was closed
- Opening time of the site for which the incident was logged
- Closing Time of the site for which incident was logged
- Country of the site for which incident has been logged
Assumption
It is assumed that opening and closing times are same on all working days and that all the sites are closed on holidays and weekends
Function should take all the above five "given" as parameter and then calculate age of the incident.
Example of problem¶
Let's say an incident was logged on "Friday 10th June 2016 at 12:00" for a site in the "UK" which opens between 09:00 to 16:00. This incident was then closed on "Tuesday 14th June 2016 at 14:00".
For the above incident function should calculate the age as 960 minutes = 16 hours = [4 hours on Friday (12:00 to 16:00) + 7 hours on Monday (09:00 to 16:00) + 5 hours on Tuesday (09:00 to 14:00)]
Pre-requisites:¶
A holiday table for the "Country" of the site for which incident is being provided should already be created on the database with the name holiday_table
. Created using code below:
Sample sql-insert Data for holiday_table:
Function code and explanation¶
Calling the function¶
Function will expect 5 parameters and with specific format as explained below:
- param_country - This is the country code as specified in holiday table
- assigneddatetime - This must be provided in the format
%Y-%m-%d %H-%i-%s
. So for our example it will be 2016-06-10 12:00:00 - closeddatetime - This must be provided in the format
%Y-%m-%d %H-%i-%s
. So for our example it will be 2016-06-14 14:00:00 - starttime - This must be in the format
%H:%i
. So for our example it will be 09:00 - endtime - This must be in the format
%H:%i
. So for our example it will be 16:00
The call for this function will be as below:
Complete Flowchart¶
The plantuml code for this can be checked by copying the image link and decoding it on Plantuml Online Server
Comments from Disqus
As I plan to move away from disqus comment system, I am just including the screenshots of comments just so any information shared by or to the readers is retained.