calculate hours based on business hours in Oracle SQL
You can directly calculate the difference in hours:
SELECT task,
start_time,
end_time,
ROUND(
(
-- Calculate the full weeks difference from the start of ISO weeks.
( TRUNC( end_time, 'IW' ) - TRUNC( start_time, 'IW' ) ) * (10/24) * (6/7)
-- Add the full days for the final week.
+ LEAST( TRUNC( end_time ) - TRUNC( end_time, 'IW' ), 6 ) * (10/24)
-- Subtract the full days from the days of the week before the start date.
- LEAST( TRUNC( start_time ) - TRUNC( start_time, 'IW' ), 6 ) * (10/24)
-- Add the hours of the final day
+ LEAST( GREATEST( end_time - TRUNC( end_time ) - 8/24, 0 ), 10/24 )
-- Subtract the hours of the day before the range starts.
- LEAST( GREATEST( start_time - TRUNC( start_time ) - 8/24, 0 ), 10/24 )
)
-- Multiply to give minutes rather than fractions of full days.
* 24,
15 -- Number of decimal places
) AS work_day_hours_diff
FROM your_table;
Which, for your sample data:
CREATE TABLE your_table ( TASK, START_TIME, END_TIME ) AS
SELECT 'A', DATE '2017-01-16' + INTERVAL '10:00' HOUR TO MINUTE, DATE '2017-01-23' + INTERVAL '11:35' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'B', DATE '2017-01-18' + INTERVAL '17:53' HOUR TO MINUTE, DATE '2017-01-19' + INTERVAL '08:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'C', DATE '2017-01-13' + INTERVAL '13:00' HOUR TO MINUTE, DATE '2017-01-17' + INTERVAL '14:52' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT 'D', DATE '2017-01-21' + INTERVAL '10:00' HOUR TO MINUTE, DATE '2017-01-30' + INTERVAL '08:52' HOUR TO MINUTE FROM DUAL;
Outputs (with the date format YYYY-MM-DD HH24:MI:SS (DY)):
TASK | START_TIME | END_TIME | WORK_DAY_HOURS_DIFF
:--- | :------------------------ | :------------------------ | ------------------:
A | 2017-01-16 10:00:00 (MON) | 2017-01-23 11:35:00 (MON) | 61.583333333333333
B | 2017-01-18 17:53:00 (WED) | 2017-01-19 08:00:00 (THU) | .116666666666667
C | 2017-01-13 13:00:00 (FRI) | 2017-01-17 14:52:00 (TUE) | 31.866666666666667
D | 2017-01-21 10:00:00 (SAT) | 2017-01-30 08:52:00 (MON) | 68.866666666666667
db<>fiddle here
Previous solution:
You can use a correlated hierarchical query to generate one row for each work day and then sum the hours for each day:
SELECT task,
COALESCE( SUM( end_time - start_time ), 0 ) * 24 AS total_hours
FROM (
SELECT task,
GREATEST( t.start_time, d.column_value + INTERVAL '8' HOUR ) AS start_time,
LEAST( t.end_time, d.column_value + INTERVAL '18' HOUR ) AS end_time
FROM your_table t
LEFT OUTER JOIN
TABLE(
CAST(
MULTISET(
SELECT TRUNC( t.start_time + LEVEL - 1 )
FROM DUAL
WHERE TRUNC( t.start_time + LEVEL - 1 ) - TRUNC( t.start_time + LEVEL - 1, 'iw' ) < 6
CONNECT BY TRUNC( t.start_time + LEVEL - 1 ) < t.end_time
) AS SYS.ODCIDATELIST
)
) d
ON ( t.end_time > d.column_value + INTERVAL '8' HOUR
AND t.start_time < d.column_value + INTERVAL '18' HOUR )
)
GROUP BY task;
Calculating work hours in oracle
You can just subtract dates and multiply by 24 this will give you hours. Then divide by 8 and you get your working days.
with d as (select sysdate end_date, sysdate - 2.4 as start_date from dual)
select (end_date - start_date) * 24 / 8 from d;
In fact you can multiply by 3:
with d as (select sysdate end_date, sysdate - 2.4 as start_date from dual)
select (end_date - start_date) * 3 from d;
So in your case you can use to_date instead of to_timestamp and just subtract values.
Your query is:
SELECT DISTINCT sno, start_time,end_time,
(TO_DATE (end_time,'DD-MM-YYYY HH24:MI:SS') - TO_DATE(start_time,'DD-MM-YYYY HH24:MI:SS')) * 3 AS time_diff_work_days
FROM employee WHERE TO_CHAR((start_time),'MM-YYYY')='10-2016' AND emp_no ='18754';
Calculate Business Hours Between Two Dates without Creating Function or View
Starting with your example of 8AM Friday through 9AM Monday:
with dates as (
Select timestamp '2019-05-31 08:00:00' start_date
, timestamp '2019-06-03 09:00:00' end_date
from dual
)
We need to generate the days in between. We can do that with a recursive query:
, recur(start_date, calc_date, end_date) as (
-- Anchor Part
select start_date
, trunc(start_date)
, end_date
from dates
-- Recrusive Part
union all
select start_date
, calc_date+1
, end_date
from recur
where calc_date+1 < end_Date
)
From that we need to figure out a few things like, is the calc_day a weekday or a weekend, and what are the starting and ending times for the calc_day, we can then take those values and use a little date arithmetic to find the number of hours worked on that day (returned as day to second interval since we started with timestamps):
, days as (
select calc_date
, case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end isWeekDay
, greatest(start_date, calc_date + interval '8' hour) start_time
, least(end_date, calc_date + interval '16:30' hour to minute) end_time
, least( ( least(end_date, calc_date + interval '16:30' hour to minute)
- greatest(start_date, calc_date + interval '8' hour)
) * case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
, interval '8' hour
) daily_hrs
from recur
where start_date < (calc_date + interval '16:30' hour to minute)
and (calc_date + interval '8' hour) < end_date
)
Note that in the above step, we've limited the daily hours to 8 hours a day, and the where clause guards against start or end days that are outside business hours. The final step is to sum the hours. Unfortunately Oracle doesn't have any native interval aggregate or analytic functions, but we can still manage by converting the intervals to seconds, summing them and then converting them back to an interval for output:
select calc_date
, daily_hrs
, numtodsinterval(sum( extract(hour from daily_hrs)*60*60
+ extract(minute from daily_hrs)*60
+ extract(second from daily_hrs)
) over (order by calc_date)
,'second') run_sum
from days;
I've done the sum above as an analytic function so we can see some of the intervening data, but if you just want the final output you can change the last part of the query to this:
select numtodsinterval(sum( extract(hour from daily_hrs)*60*60
+ extract(minute from daily_hrs)*60
+ extract(second from daily_hrs)
)
,'second') run_sum
Here's a db<>fiddle with the whole query in action. Note that in the fiddle, I've altered the DB session's NLS_TERRITORY setting to AMERICA to make the query work since the first day of the week is country specific. The second query in the fiddle replaces the territory specific function:
case when mod(to_number(to_char(calc_date,'d'))-1,6) != 0 then 1 end
with a location and language agnostic calculation:
case when (mod(mod(calc_date - next_day(date '2019-1-1',to_char(date '2019-01-06','day')),7),6)) != 0 then 1 end
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
How to calculate exact hours between two datetime fields?
The 'format' comment on your first query suggests your columns are timestamps, despite the dummy column names, as the result of subtracting two timestamps is an interval. Your second query is implicitly converting both timestamps to dates before subtracting them to get an answer as a number of days - which would be fractional if you weren't truncating them and thus losing the time portion.
You can extract the number of hours from the interval difference, and also 24 * the number of days if you expect it to exceed a day:
extract(day from (date1 - date2)) * 24 + extract(hour from (date1 - date2))
If you want to include fractional hours then you can extract and manipulate the minutes and seconds too.
You can also explicitly convert to dates, and truncate or floor after manipulation:
floor((cast(date1 as date) - cast(date2 as date)) * 24)
db<>fiddle demo
How can I get the difference in hours between two dates?
The error is because SYSDATE is already a date, there's no need to use TO_DATE() to convert it to a date.
If you don't convert it to a date:
select
24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;
And if the formatting of the dates are wrong, you can possible use two steps like:
select
24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;
Calculating due date using business hours and holidays
You need a table with valid business hours, with the weekends and holidays excluded (or marked as weekend/holiday so you can skip them.) Each row represents one day and the number of working hours for that day. Then you query the business hours table from your start date to the first (min) date where the sum(hours*60) is greater than your minutes parameter, excluding marked weekend/holiday rows. That gives you your end date.
Here's the day table:
CREATE TABLE [dbo].[tblDay](
[dt] [datetime] NOT NULL,
[dayOfWk] [int] NULL,
[dayOfWkInMo] [int] NULL,
[isWeekend] [bit] NOT NULL,
[holidayID] [int] NULL,
[workingDayCount] [int] NULL,
CONSTRAINT [PK_tblDay] PRIMARY KEY CLUSTERED
(
[dt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
here's how I populate the table with days:
CREATE PROCEDURE [dbo].[usp_tblDay]
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@Dt datetime ,
@wkInMo int,
@firstDwOfMo int,
@holID int,
@workDayCount int,
@weekday int,
@month int,
@day int,
@isWkEnd bit
set @workDayCount = 0
SET @Dt = CONVERT( datetime, '2008-01-01' )
while @dt < '2020-01-01'
begin
delete from tblDay where dt = @dt
set @weekday = datepart( weekday, @Dt )
set @month = datepart(month,@dt)
set @day = datepart(day,@dt)
if @day = 1 -- 1st of mo
begin
set @wkInMo = 1
set @firstDwOfMo = @weekday
end
if ((@weekday = 7) or (@weekday = 1))
set @isWkEnd = 1
else
set @isWkEnd = 0
if @isWkEnd = 0 and (@month = 1 and @day = 1)
set @holID=1 -- new years on workday
else if @weekday= 6 and (@month = 12 and @day = 31)
set @holID=1 -- holiday on sat, change to fri
else if @weekday= 2 and (@month = 1 and @day = 2)
set @holID=1 -- holiday on sun, change to mon
else if @wkInMo = 3 and @weekday= 2 and @month = 1
set @holID = 2 -- mlk
else if @wkInMo = 3 and @weekday= 2 and @month = 2
set @holID = 3 -- President’s
else if @wkInMo = 4 and @weekday= 2 and @month = 5 and datepart(month,@dt+7) = 6
set @holID = 4 -- memorial on 4th mon, no 5th
else if @wkInMo = 5 and @weekday= 2 and @month = 5
set @holID = 4 -- memorial on 5th mon
else if @isWkEnd = 0 and (@month = 7 and @day = 4)
set @holID=5 -- July 4 on workday
else if @weekday= 6 and (@month = 7 and @day = 3)
set @holID=5 -- holiday on sat, change to fri
else if @weekday= 2 and (@month = 7 and @day = 5)
set @holID=5 -- holiday on sun, change to mon
else if @wkInMo = 1 and @weekday= 2 and @month = 9
set @holID = 6 -- Labor
else if @isWkEnd = 0 and (@month = 11 and @day = 11)
set @holID=7 -- Vets day on workday
else if @weekday= 6 and (@month = 11 and @day = 10)
set @holID=7 -- holiday on sat, change to fri
else if @weekday= 2 and (@month = 11 and @day = 12)
set @holID=7 -- holiday on sun, change to mon
else if @wkInMo = 4 and @weekday= 5 and @month = 11
set @holID = 8 -- thx
else if @holID = 8
set @holID = 9 -- dy after thx
else if @isWkEnd = 0 and (@month = 12 and @day = 25)
set @holID=10 -- xmas day on workday
else if @weekday= 6 and (@month = 12 and @day = 24)
set @holID=10 -- holiday on sat, change to fri
else if @weekday= 2 and (@month = 12 and @day = 26)
set @holID=10 -- holiday on sun, change to mon
else
set @holID = null
insert into tblDay select @dt,@weekday,@wkInMo,@isWkEnd,@holID,@workDayCount
if @isWkEnd=0 and @holID is null
set @workDayCount = @workDayCount + 1
set @dt = @dt + 1
if datepart( weekday, @Dt ) = @firstDwOfMo
set @wkInMo = @wkInMo + 1
end
END
I also have a holiday table, but everyone's holidays are different:
holidayID holiday rule description
1 New Year's Day Jan. 1
2 Martin Luther King Day third Mon. in Jan.
3 Presidents' Day third Mon. in Feb.
4 Memorial Day last Mon. in May
5 Independence Day 4-Jul
6 Labor Day first Mon. in Sept
7 Veterans' Day Nov. 11
8 Thanksgiving fourth Thurs. in Nov.
9 Fri after Thanksgiving Friday after Thanksgiving
10 Christmas Day Dec. 25
HTH
Related Topics
Check Constraint on Multiple Columns
Postgres Function Returning Table Not Returning Data in Columns
Performance of SQL Server 2005 Query
How to Group by Date Time Column Without Taking Time into Consideration
Best Equivalent for Isinteger in SQL Server
Oracle 11G - Check Constraint with Regex
Sqlite Equivalent of Row_Number() Over (Partition by ...)
Aggregating (X,Y) Coordinate Point Clouds in Postgresql
SQL Command Not Properly Ended
Transform and Pivot in Access 2013 SQL
How to Map Input and Output Columns Dynamically in Ssis
Group by with Clob in Select-Statement
Find All Records Which Have a Count of an Association Greater Than Zero