当前位置: 首页 > 面试题库 >

如何消除Oracle的非工作时间

邓翼
2023-03-14
问题内容

我有两列DateTime类型。第一个在进程启动时存储DateTime,另一个在该进程完成时存储DateTime。我想计算完成工作所需的总工作时间。工作时间是上午10点至晚上7点,周日不营业。

以下是我简要介绍的一些摘录:

       SELECT col1, col2, floor(((date2-date1)*24*60*60)/3600)
       || ' HOURS ' ||
       floor((((date2-date1)*24*60*60) -
       floor(((date2-date1)*24*60*60)/3600)*3600)/60)
       || ' MINUTES ' ||
       round((((date2-date1)*24*60*60) -
       Floor(((date2-date1)*24*60*60)/3600)*3600 -
       (floor((((date2-date1)*24*60*60) -
       floor(((date2-date1)*24*60*60)/3600)*3600)/60)*60) ))
       || ' SECS ' Time_Difference
       From Table_Name;

和,

       Select To_Number(To_Char(date1, 'HH24')) || ':'
       ||   to_number(to_char(date1, 'MI')) || ':'||
       to_number(to_char(date1, 'SS')) from Table_Name

请帮忙。

对于您提到的上述解决方案,这就是结果!

START DATE          DAY     FINISH DATE     DAY  Date Diff   Total Hours   Work Hours

07-AUG-12 21:55:21  TUE 08-AUG-12 11:09:10  WED 0 13:13:49.0        13   13 
13-NOV-12 15:45:25  TUE 14-NOV-12 10:41:42  WED 0 18:56:17.0        18   18 
20-DEC-12 20:31:03  THU 21-DEC-12 11:03:36  FRI 0 14:32:33.0        14   14 
14-MAR-13 20:39:00  THU 15-MAR-13 11:00:04  FRI 0 14:21:4.0         14   14 
07-JUN-12 21:17:36  THU 08-JUN-12 11:02:23  FRI 0 13:44:47.0        13   13 
18-SEP-12 20:48:27  TUE 19-SEP-12 11:07:35  WED 0 14:19:8.0         14   14

问题答案:

如果我理解正确,则要计算开始日期和结束日期之间的时差,不包括上午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来忽略它。



 类似资料:
  • 常见问题:如何卸载Service Worker?对于使用JavaScript注销服务工作人员有一个很好的答案。如何让服务工作者在注销时清除任何命名缓存? 从MDN,服务工作人员将在注销之前完成任何正在进行的操作。因此,听起来应该可以监听 状态会做什么。 伸手去抓稻草,虽然我看到了卸载标志的引用,但我还没有看到它暴露在任何地方。建议?

  • 问题内容: 当我们执行此操作时,将不会有任何结果 我的问题是: 以上查询在逻辑上等效于 就像上面的声明一样,不会有任何结果 请说清楚? 问题答案: 正确(但请注意,这是一个运算符,而不是一个子句,它通常在SQL中这样工作,不仅适用于Oracle)。 等效于: 实际应该写成: 和 与以下内容相同: 结果为: 并进一步作为: 因此,它正确不返回任何行。 请注意,如果您有,它将评估为(在练习中保留),并

  • 本文向大家介绍如何确定Oracle中剩余的SQL工作量?,包括了如何确定Oracle中剩余的SQL工作量?的使用技巧和注意事项,需要的朋友参考一下 问题: 您想知道长时间运行的SQL可能需要多长时间才能完成。 解 我们可以使用“ V $SESSION_LONGOPS”视图来了解查询剩余的执行时间,“ V $SESSION_LONGOPS”视图显示已运行超过六秒钟的各种数据库操作的状态。请注意,该视

  • 我正在使用Jenkins的Pipeline(以前是Workflow)插件集合。我设法在Jenkins服务器上创建了一个无法停止的工作流作业。使用

  • 在Kotlin中,编译以下代码: 但是,该代码不: 编译此代码将导致以下错误: 在Java中,两个示例都无法编译: 不出所料,前面的两个代码片段都会产生熟悉的编译器错误: 令我惊讶的是,第一个 Kotlin 示例根本有效,其次,如果它有效,为什么第二个 Kotlin 示例会失败?Kotlin 是否将方法的返回类型视为其签名的一部分?此外,为什么 Kotlin 中的方法签名与 Java 相比,它遵循

  • 我有一个jQuery移动页面,里面有JavaScript。问题是,除非刷新页面,否则JavaScript无法工作。这是我的代码: