如果我理解正确，则要计算开始日期和结束日期之间的时差，不包括上午10点之前和下午7点之后的时间。
这是示例查询和sql小提琴。
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);
我所做的是
- 检查开始时间是否在上午10点之前，结束时间是否在晚上7点之后。如果是这样，请将时间设置为上午10点和晚上7点。
- 然后减去日期，并将结果转换为“间隔类型”。
- 然后从时间间隔中提取小时，分钟和秒。
注意： 此查询假定两个日期都在同一天，并且都不在上午10点之前或晚上7点之后。
更新： 要排除假期，查询将变得复杂。我建议编写三个函数，并在查询中使用这些函数。
第一个功能：
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;
如果开始时间不在工作时间之外，请将开始时间修改为下一个最接近的开始时间。
第二功能：
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;
如果结束时间不在工作时间范围内，请将其修改为最近的最近结束时间。
第三功能：
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;
此功能可在计算持续时间时找到要排除的天数。
因此，最终查询应该是这样的，
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);
如果持续时间小于0，请通过将其设置为0来忽略它。
