Home
SQL For Working Hours
Blog Date 13 November
Work was started on an item on say 1430 on 16 October 2023. Work ended on the item on say 1645 on 17 October 2023. In this case the minutes the product was worked on is calculated thus
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2023-10-16 14:30:00'
SET @EndDate = '2023-10-17 16:45:00'
SELECT
DATEDIFF(MINUTE, @StartDate, @EndDate)
The result of this query is 1575 minutes.
However this simple calculation does not take into account "working hours". We can see the item was worked on from mid afternoon on Monday and finished late in the day on Tuesday. Alas this factory finishes work at 1700 and opens for work again at 0900. As such the item was acutally worked on...
Mon 1430 to 1700 - 2.5 hours, 150 minutes
Tue 0900 to 1645 - 7.75 hours, 465 minutes
Total working time = 615 minutes
How can we take this into account with our code? Luckily SQL has some quirks that help.
There are 24 x 60 minutes in a day, 1440 minutes. There are 8 x 60 minutes in our example's working day, 480 minutes. So for each working day there are 1440 - 480 minutes that are not worked, 960 minutes. Remember that.
SQL's DATEDIFF(DAY, @StartDate, @EndDate) quirk is that it does not count FULL days, that is it does NOT show "1" when there are 24 full hours. It merely counts the number of days spanned? Er, hard to describe. Perhaps it's better to say how often we have crossed the midnight point. Check below.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2023-10-16 23:59:00'
SET @EndDate = '2023-10-17 00:01:00'
SELECT
DATEDIFF(DAY, @StartDate, @EndDate)
From just before midnight on the 16th to just after midnight on the 17th means we crossed the midnight delimeter once, so the output from above is 1, We are counting the number of midnights.
Considering the workshop is closed overnight we can use this to count the number of overnights. Using our original timings we can take the total time, 1575 minutes, and remove the overnight closure time of 960 minutes, leaving us
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2023-10-16 14:30:00'
SET @EndDate = '2023-10-17 16:45:00'
SELECT
DATEDIFF(MINUTE, @StartDate, @EndDate)
- (DATEDIFF(day, @StartDate, @EndDate) * 960)
Leaving us with the required 615 minutes we worked out before.
Now - let's throw another spanner in the works - weekends. Guess what! SQL also counts a week as the moveover at midnight between a Saturday and a Sunday. Below the code will return "1"
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2023-10-07 23:59:00'
SET @EndDate = '2023-10-08 00:01:00'
SELECT
DATEDIFF(WEEK, @StartDate, @EndDate)
So now we can see how many weekends we've had. Great! Just remove 2 (days) x 24 (hours) x 60 (minutes) from the total time. That'll be 2880 minutes. Wait a minute... we are already removing days (actually midnights passed). Let me think...
So Friday night is one midnight, Saturday night is another midnight. We're already removing 960 minutes time 2, 1920. We also need to remove 2 days worth of work, another 960, leaving us with 960 minutes left to remove! Who'da thunk. So here's the final code.
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2023-10-07 14:45'
SET @EndDate = '2023-10-17 10:30:00'
SELECT
DATEDIFF(MINUTE, @StartDate, @EndDate)
- (DATEDIFF(DAY, @StartDate, @EndDate) * 960)
- (DATEDIFF(WEEK, @StartDate, @EndDate) * 960)
Reader's Comments
Name
Comment
Add a RELEVANT link (not required)
Upload an image (not required)
Uploading...
Home