How to eliminate non-working hours in Oracle
If i understand correctly, you want to calculate the difference between the start and finish date excluding the time before 10 am and after 7 pm.
Here's the sample query and sql fiddle.
SELECT start_time,
finish_time,
interval_time,
EXTRACT (HOUR FROM interval_time), --extract the hours,mins and seconds from the interval
EXTRACT (MINUTE FROM interval_time),
EXTRACT (SECOND FROM interval_time)
FROM (SELECT start_time,
finish_time,
NUMTODSINTERVAL (
CASE
WHEN finish_time - TRUNC (finish_time) > (19 / 24) --if finish time is after 7pm
THEN
TRUNC (finish_time) + (19 / 24) --set it to 7pm
ELSE
finish_time --else set it to actual finish time
END
- CASE
WHEN start_time - TRUNC (start_time) < (10 / 24) --if start time is before 10 am
THEN
TRUNC (start_time) + (10 / 24) --set it to 10 am.
ELSE
start_time --else set it to the actual start time
END,
'day') --subtract the both and convert the resulting day to interval
interval_time
FROM timings);
What I have done is,
- Check if the start time is before 10 am and finish time is after 7 pm. If so, set the time to 10 am and 7 pm.
- Then subtract the dates and convert the resulting days to Interval Type.
- Then extract the hours, mins and seconds from the Interval.
Note: This query assumes that both dates fall on same day and both are not before 10am or after 7 pm.
UPDATE:
To exclude holidays, the query will become complicated. I suggest writing three functions and use these functions in the query.
1st function:
FUNCTION modify_start_time (p_in_dte DATE) RETURN DATE
----------------------------------
IF p_in_dte - TRUNC (p_in_dte) < (10 / 24)
THEN
RETURN TRUNC (p_in_dte) + (10 / 24);
ELSIF p_in_dte - TRUNC (p_in_dte) > (19 / 24)
THEN
RETURN TRUNC (p_in_dte) + 1 + (10 / 24);
ELSE
RETURN p_in_dte;
END IF;
If the start time is outside the work hours, modify the start time to next nearest start time.
2nd function:
FUNCTION modify_finish_time (p_in_dte DATE) RETURN DATE
----------------------------------
IF p_in_dte - TRUNC (p_in_dte) > (19 / 24)
THEN
RETURN TRUNC (p_in_dte) + (19 / 24);
ELSIF p_in_dte - TRUNC (p_in_dte) < (10 / 24)
THEN
RETURN TRUNC (p_in_dte) - 1 + (19 / 24);
ELSE
RETURN p_in_dte;
END IF;
If the finish time is outside the work hours, modify it to the previous nearest finish time.
3rd function:
FUNCTION get_days_to_exclude (p_in_start_date DATE,
p_in_finish_date DATE) RETURN NUMBER
--------------------------------------------------------
WITH cte --get all days between start and finish date
AS ( SELECT p_in_start_date + LEVEL - 1 dte
FROM DUAL
CONNECT BY LEVEL <= p_in_finish_date + 1 - p_in_starT_date)
SELECT COUNT (1) * 9 / 24 --mutiply the days with work hours in a day
INTO l_num_holidays
FROM cte
WHERE TO_CHAR (dte, 'dy') = 'sun' --find the count of sundays
OR dte IN --fins the count of holidays, assuming leaves are stored in separate table
(SELECT leave_date
FROM leaves
WHERE leave_date BETWEEN p_in_start_date
AND p_in_finish_date);
l_num_holidays :=
l_num_holidays + ( (p_in_finish_date - p_in_start_date) * (15 / 24)); --also, if the dates span more than a day find the non working hours.
RETURN l_num_holidays;
This function finds the no of days to be excluded while calculating the duration.
So, the final query should be something like this,
SELECT start_time,
finish_time,
CASE
WHEN work_duration < 0 THEN NUMTODSINTERVAL (0, 'day')
ELSE NUMTODSINTERVAL (work_duration, 'day')
END
FROM (SELECT start_time, finish_time,
--modify_start_time (start_time), modify_finish_time (finish_time),
modify_finish_time (finish_time)
- modify_start_time (start_time)
- get_days_to_exclude (
TRUNC (modify_start_time (start_time)),
TRUNC (modify_finish_time (finish_time)))
work_duration
FROM timings);
If the duration is less than 0, ignore it by setting it to 0.
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
oracle SQL how to remove time from date
When you convert your string to a date you need to match the date mask to the format in the string. This includes a time element, which you need to remove with truncation:
select
p1.PA_VALUE as StartDate,
p2.PA_VALUE as EndDate
from WP_Work p
LEFT JOIN PARAMETER p1 on p1.WP_ID=p.WP_ID AND p1.NAME = 'StartDate'
LEFT JOIN PARAMETER p2 on p2.WP_ID=p.WP_ID AND p2.NAME = 'Date_To'
WHERE p.TYPE = 'EventManagement2'
AND trunc(TO_DATE(p1.PA_VALUE, 'DD-MM-YYYY HH24:MI')) >= TO_DATE('25/10/2012', 'DD/MM/YYYY')
AND trunc(TO_DATE(p2.PA_VALUE, 'DD-MM-YYYY HH24:MI')) <= TO_DATE('26/10/2012', 'DD/MM/YYYY')
Outside the scope of the question, but storing dates as strings is bad practice, and storing date times is even worse.
- We need to convert the strings to dates in order to do any form of date processing (arithmetic, interval assessment, etc) on them
- Strings offer no guarantees regarding format, so we run the risk of date corruption crashing our code. We can defend against this by employing
VALIDATE_CONVERSION()(available since 12c, find out more ) but it's still a PITN - Using non-standard datatypes makes it harder to reason about the data model and the code we build over it.
Oracle SQL Query to fetch non-working hours in a given month
WITH Trip_Dates ( Start_Date, End_Date ) AS (
SELECT TO_DATE( '2016-02-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS' ), TO_DATE( '2016-02-06 20:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL UNION ALL
SELECT TO_DATE( '2016-02-01 04:00:00', 'YYYY-MM-DD HH24:MI:SS' ), TO_DATE( '2016-02-02 10:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL
)
SELECT ( ADD_MONTHS( TRUNC( SYSDATE, 'MM' ), 1 ) - TRUNC( SYSDATE, 'MM' )
- SUM( End_Date - Start_Date ) ) * 24 AS Unworked_Hours
FROM Trip_Dates;
Outputs:
UNWORKED_HOURS
--------------
564
Start Date Calculated Given End Date and Working Hours PL/SQL
The following code could be a starting point:
function task_start_date(
p_due_date date,
p_working_hours number
) return date
is
l_start_date date;
begin
-- Subtract full days
l_start_date := p_due_date - trunc(p_working_hours / 8);
-- Subtract remaining hours
l_start_date := l_start_date - mod(p_working_hours, 8) / 24;
-- Fix date if the due date is before 8AM
if to_number(to_char(l_start_date, 'HH24')) < 8 then
l_start_date := l_start_date - 15 / 24;
end if;
return l_start_date;
end task_start_date;
Note that the function does not consistently consider lunch time. You'll need to define exactly when lunch time is and adapt the function accordingly.
Calculate business days in Oracle SQL(no functions or procedure)
The solution, finally:
SELECT OrderNumber, InstallDate, CompleteDate,
(TRUNC(CompleteDate) - TRUNC(InstallDate) ) +1 -
((((TRUNC(CompleteDate,'D'))-(TRUNC(InstallDate,'D')))/7)*2) -
(CASE WHEN TO_CHAR(InstallDate,'DY','nls_date_language=english')='SUN' THEN 1 ELSE 0 END) -
(CASE WHEN TO_CHAR(CompleteDate,'DY','nls_date_language=english')='SAT' THEN 1 ELSE 0 END) as BusinessDays
FROM Orders
ORDER BY OrderNumber;
Thanks for all your responses !
Oracle: how to check if system date not in working hours
found a working solution. it may help to someone.
IF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT','SUN')
THEN
raise_application_error(-20000, 'message...');
ELSIF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('FRI') AND to_number(to_char(sysdate, 'hh24MIss')) > 170000
THEN
raise_application_error(-20000, 'message...');
ELSIF to_char(sysdate, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('MON') AND to_number(to_char(sysdate, 'hh24MIss')) < 80000
THEN
raise_application_error(-20000, 'message...');
END IF;
Related Topics
Group by Every N Records in T-Sql
Get the Number of Affected Rows in a MySQL Update Statement
How to Force Oracle to Change a Query's Plan Without Using Hints
How to Migrate Datetime Values to Datetimeoffset in SQL Server
Swap Values for Two Rows in the Same Table in SQL Server
How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format
SQL Do Inner Join If Condition Met
Sqlite3 (Or General SQL) Retrieve Nth Row of a Query Result
Multiple SQL Update Statements in Single Query
There Is Already an Object Named '#Tmptable' in the Database
How to Calculate Balances in an Accounting Software Using Postgres Window Function
Always Show Decimal Places in SQL