Calculate time difference (only working hours) in minutes between two dates
If you want to do it pure SQL here's one approach
CREATE TABLE working_hours (start DATETIME, end DATETIME);
Now populate the working hours table with countable periods, ~250 rows per year.
If you have an event(@event_start, @event_end) that will start off hours and end off hours then simple query
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
will suffice.
If on the other hand the event starts and/or ends during working hours the query is more complicated
SELECT SUM(duration)
FROM
(
SELECT SUM(end-start) as duration
FROM working_hours
WHERE start >= @event_start AND end <= @event_end
UNION ALL
SELECT end-@event_start
FROM working_hours
WHERE @event_start between start AND end
UNION ALL
SELECT @event_end - start
FROM working_hours
WHERE @event_end between start AND end
) AS u
Notes:
- the above is untested query, depending on your RDBMS you might need date/time functions for aggregating and subtracting datetime (and depending on the functions used the above query can work with any time precision).
- the query can be rewritten to not use the UNION ALL.
- the working_hours table can be used for other things in the system and allows maximum flexibility
EDIT:
In MSSQL you can use DATEDIFF(mi, start, end) to get the number of minutes for each subtraction above.
Calculate business hours between two dates
Baran's answer fixed and modified for SQL 2005
SQL 2008 and above:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATE
SET @FirstDay = CONVERT(DATE, @StartDate, 112)
DECLARE @LastDay DATE
SET @LastDay = CONVERT(DATE, @FinishDate, 112)
DECLARE @StartTime TIME
SET @StartTime = CONVERT(TIME, @StartDate)
DECLARE @FinishTime TIME
SET @FinishTime = CONVERT(TIME, @FinishDate)
DECLARE @WorkStart TIME
SET @WorkStart = '09:00'
DECLARE @WorkFinish TIME
SET @WorkFinish = '17:00'
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATE
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATE
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
SQL 2005 and below:
-- =============================================
-- Author: Baran Kaynak (modified by Kodak 2012-04-18)
-- Create date: 14.03.2011
-- Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar.
-- =============================================
CREATE FUNCTION [dbo].[WorkTime]
(
@StartDate DATETIME,
@FinishDate DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @Temp BIGINT
SET @Temp=0
DECLARE @FirstDay DATETIME
SET @FirstDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @StartDate))
DECLARE @LastDay DATETIME
SET @LastDay = DATEADD(dd, 0, DATEDIFF(dd, 0, @FinishDate))
DECLARE @StartTime DATETIME
SET @StartTime = @StartDate - DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
DECLARE @FinishTime DATETIME
SET @FinishTime = @FinishDate - DATEADD(dd, DATEDIFF(dd, 0, @FinishDate), 0)
DECLARE @WorkStart DATETIME
SET @WorkStart = CONVERT(DATETIME, '09:00', 8)
DECLARE @WorkFinish DATETIME
SET @WorkFinish = CONVERT(DATETIME, '17:00', 8)
DECLARE @DailyWorkTime BIGINT
SET @DailyWorkTime = DATEDIFF(MINUTE, @WorkStart, @WorkFinish)
IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END
IF (@FinishTime<@WorkStart)
BEGIN
SET @FinishTime=@WorkStart
END
IF (@StartTime>@WorkFinish)
BEGIN
SET @StartTime = @WorkFinish
END
DECLARE @CurrentDate DATETIME
SET @CurrentDate = @FirstDay
DECLARE @LastDate DATETIME
SET @LastDate = @LastDay
WHILE(@CurrentDate<=@LastDate)
BEGIN
IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + @DailyWorkTime
END
--IF it starts at startdate and it finishes not this date find diff between work finish and start as minutes
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)
END
ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)
END
--IF it starts and finishes in the same date
ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)
END
END
SET @CurrentDate = DATEADD(day, 1, @CurrentDate)
END
-- Return the result of the function
IF @Temp<0
BEGIN
SET @Temp=0
END
RETURN @Temp
END
Calculate working hours between two dates based on business hours
There are multiple phases to this calculation. It may be helpful to look at a calendar for the explanation.
- Weeks between start and end
DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE) As NumWeeks
This is calculating the difference between the rows of a calendar, a week. (a 7 day period generally represented as a single row on a calendar). Saturday and the following Sunday may only be one day apart, but they are in separate weeks, and therefore are in separate rows on the calendar.
- Week Days between start and end.
DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE) as NumDays
Step 1 calculated the difference in rows of the calendar, step 2 we're calculating the difference in columns. This will account for partial week differences.
Tuesday to the following Monday is six days, one day less than a week. Step one returned 1 week. Since we're shifting one column to the left, we adjusting the week by -1 days. If the end date had been Wednesday, it would be 1 week plus 1 day, but since it is Monday, it is 1 week minus 1 day.
- Normalizing the Start/End time outside of working hours
CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.START_DATE AS TIME)
END AS DATETIME) as StartTimeOnly,
CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.END_DATE AS TIME)
END AS DATETIME) as EndTimeOnly
This calculation is only interested in the time, so we cast to Time, then back to DateTime. This sets the Date component for both to 1900-01-01.
Similar to the week & day relationship, an End Time that occurs the next day, but before the Start Time will subtract hours credited from the number of days. For example 1/2 at 12:00 to 1/3 at 10:00 would be 1 day(1/3 - 1/2), or 8 hours, - 2 hours (10-12) = 6 hours of business time.
- Calculating the difference in minutes, to ensure partial hours are considered correctly, then converting to hours. This ensures times only a couple minutes apart across an hour boundary don't get counted as a full hour. Of course, the trade off is 59 minutes rounds down to 0 hours.
DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly)/60 as NumHours
If rounding at the half hour...
CAST(ROUND(DATEDIFF(MINUTE, StartTimeOnly, EndTimeOnly) / 60.0, 0) AS INT) AS RoundedHours
- Wrap it all together
,
cte2 AS
(
SELECT CTE.START_DATE,
CTE.END_DATE,
StartTimeOnly = CAST(CASE WHEN CAST(CTE.START_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.START_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.START_DATE AS TIME)
END AS DATETIME),
EndTimeOnly = CAST(CASE WHEN CAST(CTE.END_DATE AS TIME) < '08:00' THEN '08:00'
WHEN CAST(CTE.END_DATE AS TIME) > '16:00' THEN '16:00'
ELSE CAST(CTE.END_DATE AS TIME)
END AS DATETIME)
FROM CTE
),
CTE3 AS
(
SELECT START_DATE = CAST(cte2.START_DATE AS DATETIME2(0)),
END_DATE = CAST(cte2.END_DATE AS DATETIME2(0)),
NumWeeks = DATEDIFF(WEEK, cte2.START_DATE, cte2.END_DATE),
NumDays = DATEPART(WEEKDAY, cte2.END_DATE) - DATEPART(WEEKDAY, cte2.START_DATE),
NumHours = DATEDIFF(MINUTE, cte2.StartTimeOnly, cte2.EndTimeOnly)/60
FROM cte2
)
SELECT CTE3.START_DATE,
CTE3.END_DATE,
CTE3.NumWeeks,
CTE3.NumDays,
CTE3.NumHours,
TotalBusinessHours = (CTE3.NumWeeks * 5 * 8) + (CTE3.NumDays * 8) + (CTE3.NumHours )
FROM CTE3
;
For more accurate results, you'll also want to add a table containing your holidays. You'll then subtract the number of holidays found between your start and end date from your total number of days, before converting it to hours.
A question you may still need to answer... what happens if the start and end dates occur during non-working hours? e.g. Start at 19:00 and finish at 20:00. Is that 0 business hours to resolve?
calculating time taken between dates only during business hours
I already have created a function to get a list of working days between 2 dates (inclusive), minus holidays and weekends as follows:
Function GetWorkingDates(startDate As Date, endDate As Date, holidayDates As Date()) As List(Of Date)
If endDate < startDate Then
Throw New ArgumentOutOfRangeException("endDate", "Value must be equal or larger than 'startDate' parameter.")
End If
Dim listDate As List(Of Date) = Enumerable.Range(0, 1 + CInt((endDate - startDate).TotalDays)).Select(Function(n) startDate.AddDays(n)).ToList
' if defined, remove holidays from listDate
If holidayDates IsNot Nothing Then listDate.RemoveAll(Function(d) holidayDates.Contains(d))
' remove weekends as defined below
Dim weekends As DayOfWeek() = {DayOfWeek.Saturday, DayOfWeek.Sunday}
listDate.RemoveAll(Function(d) weekends.Contains(d.DayOfWeek))
Return listDate
End Function
To get total hours, I just created a new function to get the total timespan from the result of my existing function above:
Function GetTotalWorkingTimeSpan(startDateTime As Date, endDateTime As Date, startWorkTime As TimeSpan, endWorkTime As TimeSpan, holidayDates As Date()) As TimeSpan
If endDateTime < startDateTime Then
Throw New ArgumentOutOfRangeException("endDate", "Value must be equal or larger than 'startDate' parameter.")
End If
If endWorkTime < startWorkTime Then
Throw New ArgumentOutOfRangeException("endWorkTime", "Value must be equal or larger than 'startWorkTime' parameter.")
End If
' get list of working days minus weekends and holidays
Dim lstWorkDays As List(Of Date) = GetWorkingDates(startDateTime.Date, endDateTime.Date, holidayDates)
' get total minutes by bultiplying total working days and total minutes per day
Dim totalMinutes As Double = lstWorkDays.Count * (endWorkTime - startWorkTime).TotalMinutes
' deduct the first day's hour if occured later than the startWorkTime, only if startDateTime is a working day
If lstWorkDays.Contains(startDateTime.Date) Then
Dim minutesOffset As Double = (startDateTime.TimeOfDay - startWorkTime).TotalMinutes
If minutesOffset > 0 Then totalMinutes -= minutesOffset
End If
' deduct the last day's hour if occured ealier than the endWorkTime, only if endDateTime is a working day
If lstWorkDays.Contains(endDateTime.Date) Then
Dim minutesOffset As Double = (endWorkTime - endDateTime.TimeOfDay).TotalMinutes
If minutesOffset > 0 Then totalMinutes -= minutesOffset
End If
Return TimeSpan.FromMinutes(totalMinutes)
End Function
Using your data, I created a test console:
Sub Main()
Dim sdt As Date = #5/27/2013 6:00:00 AM#
Dim edt As Date = #5/28/2013 10:30:00 AM#
Dim hols() As Date = {} ' add holiday dates here
Dim lst As List(Of Date) = GetWorkingDates(sdt, edt, Nothing) ' or simply Nothing to not check for holidays
Console.WriteLine("Num of working days = " & lst.Count)
Console.WriteLine()
Console.WriteLine("List of working dates:")
lst.ForEach(Sub(d) Console.WriteLine("* " & d.ToLongDateString))
Console.WriteLine()
Dim totalWorkingTimeSpan As TimeSpan = GetTotalWorkingTimeSpan(sdt, edt, New TimeSpan(9, 0, 0), New TimeSpan(17, 0, 0), hols)
Console.WriteLine("Total working hours = " & totalWorkingTimeSpan.TotalMinutes & " minutes, or " & totalWorkingTimeSpan.TotalHours & " hours")
PromptExit()
End Sub
Output of console app:
How to calculate business hours between two dates when business hours vary depending on the day in R?
Here is something you can try with lubridate. I edited another function I had I thought might be helpful.
First create a sequence of dates between the two dates of interest. Then create intervals based on business hours, checking each date if on the weekend or not.
Then, "clamp" the start and end times to the allowed business hours time intervals using pmin and pmax.
You can use time_length to get the time measurement of the intervals; summing them up will give you total time elapsed.
library(lubridate)
library(dplyr)
calc_bus_hours <- function(start, end) {
my_dates <- seq.Date(as.Date(start), as.Date(end), by = "day")
my_intervals <- if_else(weekdays(my_dates) %in% c("Saturday", "Sunday"),
interval(ymd_hm(paste(my_dates, "09:00"), tz = "UTC"), ymd_hm(paste(my_dates, "21:00"), tz = "UTC")),
interval(ymd_hm(paste(my_dates, "08:00"), tz = "UTC"), ymd_hm(paste(my_dates, "23:00"), tz = "UTC")))
int_start(my_intervals[1]) <- pmax(pmin(start, int_end(my_intervals[1])), int_start(my_intervals[1]))
int_end(my_intervals[length(my_intervals)]) <- pmax(pmin(end, int_end(my_intervals[length(my_intervals)])), int_start(my_intervals[length(my_intervals)]))
sum(time_length(my_intervals, "hour"))
}
calc_bus_hours(as.POSIXct("07/24/2020 22:20", format = "%m/%d/%Y %H:%M", tz = "UTC"), as.POSIXct("07/25/2020 21:20", format = "%m/%d/%Y %H:%M", tz = "UTC"))
[1] 12.66667
Edit: For Spanish language, use c("sábado", "domingo") instead of c("Saturday", "Sunday")
For the data frame example, you can use mapply to call the function using the two selected columns as arguments. Try:
df$business_hours <- mapply(calc_bus_hours, df$start_date, df$end_date)
start end business_hours
1 2020-07-24 22:20:00 2020-07-25 21:20:00 12.66667
2 2020-07-14 21:00:00 2020-07-16 09:30:00 18.50000
3 2020-07-18 08:26:00 2020-07-19 10:00:00 13.00000
4 2020-07-10 08:00:00 2020-07-13 11:00:00 42.00000
Oracle SQL - How to work out the working hours and minutes between two dates
I did not check for possible duplicates, but one way to do it is recursive query:
with cte(id, start_date, end_date, hd1, hd2) as (
select id, cast(start_date as date), cast(end_date as date), cast(start_date as date),
cast(least(end_date, trunc(start_date) + 17/24) as date)
from jobs
union all
select id, start_date, end_date, cast(trunc(hd1) + 1 + 8/24 as date),
cast(least(trunc(hd1) + 1 + 17/24, end_date) as date)
from cte
where trunc(hd1) + 1 + 8/24 < end_date)
select id, start_date, end_date, dbms_xplan.format_time_s(sum(hd2 - hd1) * 24 * 60) hours
from cte
where to_char(hd1, 'd') not in (6, 7)
group by id, start_date, end_date
dbfiddle demo
Where I use 17/24 it means ending hour 17:00, 8/24 - starting hour, not in (6, 7) excludes saturdays and sundays.
Edit 1: It should be 24 * 60 * 60 in last select.
Edit 2: To make query independent of nls_settings use:
to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun')
dbfiddle demo
the version of Oracle I'm using insists on having SELECT as the first
word in any code
Such recursive queries are available from Oracle version 11. I don't know if your tool requires select in the first line or is this version problem, but in first case you can move RCTE to from clause:
select id, start_date, end_date, dbms_xplan.format_time_s(sum(hd2 - hd1) * 24 * 60 * 60) hours
from (
with
cte(id, start_date, end_date, hd1, hd2) as (
select id, cast(start_date as date), cast(end_date as date), cast(start_date as date),
cast(least(end_date, trunc(start_date) + 17/24) as date)
from jobs
union all
select id, start_date, end_date, cast(trunc(hd1) + 1 + 8/24 as date),
cast(least(trunc(hd1) + 1 + 17/24, end_date) as date)
from cte
where trunc(hd1) + 1 + 8/24 < end_date)
select * from cte
where to_char(hd1, 'Dy', 'nls_date_language=english') not in ('Sat', 'Sun') )
group by id, start_date, end_date
fiddle
Related Topics
How to Have Multiple Pivots Using the Same Pivot Column Using SQL Server
How to Change Date Format in Hive
Does Limiting a Query to One Record Improve Performance
How to Calculate a Moving Average Using MySQL
How to Find the .Net Framework Version Used in an Ssis 2008 R2 Package
SQL Constraint: Date a Is Before Date B -- How
Why Can Pl/Pgsql Functions Have Side Effect, While SQL Functions Can'T
How to Get the Latest 2 Items Per Category in One Select (With MySQL)
Best Way to Work with Transactions in Ms SQL Server Management Studio
Create a One to Many Relationship Using SQL Server
SQL Use Case Statement in Where in Clause
SQL Server 2008 Express Concat() Doesn't Exist
Postgres Syntax Error at or Near "If"
Oracle Connect by Clause Equivalent in SQL Server
Bigquery Group_Concat and Order By
Is SQL Order by Clause Guaranteed to Be Stable ( by Standards)