Oracle显示超过24小时 [英] Oracle display more than 24 hours

问题描述

我有一张表，里面有两个 DATE 列 END_TIME 和 START_TIME 。在此表中，我运行以下查询：

  SELECT y.ID，
 TO_CHAR（TO_DATE（'00：00 ：00'，'HH24：MI：SS'）+（y.END_TIME  -  y.START_TIME）
，'HH24：MI：SS'）AS RUNTIME，
 y.END_TIME  -  y.START_TIME AS RUNTIME2，
 TO_CHAR（y.START_TIME，DD-MON-YYYY HH24：MI：SS）AS START_TIME，
 TO_CHAR（y.END_TIME，'DD-MON-YYYY HH24：MI：SS' ）AS END_TIME 
 FROM mytable y; 
  

我得到这两行结果：

  ID | RUNTIME | RUNTIME2 | START_TIME | END_TIME 
 --------------------------------------------- -------------------------------------------------- ------- 
 1 | 04：26：17 | 0.1849189814814814814814814814814814814815 | 30-JAN-2015 19：45：48 | 31-JAN-2015 00:12:05 
 2 | 03：28：18 | 1.14465277777777777777777777777777777778 | 06-FEB-2015 20：47：22 | 08-FEB-2015 00:15:40 
  

如您所见， ID 2 的运行时间大于24小时。如何更改我的查询，以便 RUNTIME 为 ID 2 将显示 27:28： 18

解决方案

你需要将时差分成几个小时，分数和第二个元素，将天数* 24与小时数相结合，并将其粘在一起。

当减去日期时，您将得到差异天，所以您需要将小数部分转换为其他元素，您可以使用trunc和mod的组合来实现;使用CTE使这一点更容易遵循，并显示每个价值eon自己，并组合成一个单一的字符串：

  （
选择id，end_time  -  start_time作为运行时
从mytable 
）
选择id，
运行时，
 trunc（运行时）作为天
 24 * trunc（runtime）as day_hours，
 trunc（24 * mod（runtime，1））as hours，
 trunc（60 * mod（24 *（runtime））1） ）作为分钟，
 trunc（60 * mod（24 * 60 *（运行时），1））作为秒，
 lpad（24 * trunc（runtime）
 + trunc（24 * mod （运行时，1）），2，'0'）
 ||'：'|| lpad（trunc（60 * mod（24 *（runtime），1）），2，'0'）
 ||'：'|| lpad（trunc（60 * mod（24 * 60 *（运行时），1）），2，'0'）
作为运行时
 from y; 
 
 ID RUNTIME DAYS DAY_HOURS HOURS MINUTES SECONDS RUNTIME 
 ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- 
 1 .184918981 0 0 4 26 16 04:26:16 
 2 1.14465278 1 24 3 28 18 27:28:18 
  

您还可以将日期转换为计算的时间戳，它为您提供间隔类型，然后使用提取函数获取元素代替;主要是相同的：

  with y as（
 select id，
 cast（end_time as时间戳）作为运行时的$（$）
 
 
 
运行时
提取（从运行时的一天）为天，
 24 *提取（从运行时的一天）作为day_hours，
提取（从运行时的小时）为小时，
提取（从运行时分钟）为分钟，
提取（从运行时的第二个）为秒，
 lpad（24 *提取（从运行时的一天）+提取（从运行时的小时），2，'0'）
 ||'：'|| lpad（提取（从运行时分钟），2，'0'）
 ||'：'|| lpad（提取（从运行时的第二个），2，'0'）
作为运行时
从y; 
 
 ID RUNTIME DAYS DAY_HOURS HOURS MINUTES SECONDS RUNTIME 
 ---------- ----------- --------- -  ---------- ---------- ---------- ---------- -------- 
 1 0 4：26：17.0 0 0 4 26 17 04:26:17 
 2 1 3：28：18.0 1 24 3 28 18 27:28:18 
  

或略有变化，从日期中获取差异，然后将其转换为间隔：

$ b $ （
select id，
numtodsinterval（end_time - start_time，'DAY'）作为运行时
from mytable
）
...

SQL Fiddle演示。

I have a table with two DATE columns END_TIME and START_TIME. On this table I run the following query:

SELECT y.ID,
       TO_CHAR(  TO_DATE('00:00:00', 'HH24:MI:SS') + (y.END_TIME - y.START_TIME) 
               , 'HH24:MI:SS') AS RUNTIME,
       y.END_TIME - y.START_TIME AS RUNTIME2,
       TO_CHAR(y.START_TIME, 'DD-MON-YYYY HH24:MI:SS') AS START_TIME,
       TO_CHAR(y.END_TIME, 'DD-MON-YYYY HH24:MI:SS') AS END_TIME
FROM mytable y;

I get these two rows as a result:

ID | RUNTIME | RUNTIME2                                   | START_TIME          | END_TIME
------------------------------------------------------------------------------------------------------
 1 | 04:26:17| 0.1849189814814814814814814814814814814815 | 30-JAN-2015 19:45:48| 31-JAN-2015 00:12:05
 2 | 03:28:18| 1.14465277777777777777777777777777777778   | 06-FEB-2015 20:47:22| 08-FEB-2015 00:15:40

As you can see, ID 2 had a runtime larger than 24 hours. How can I change my query so that RUNTIME for ID 2 will display 27:28:18 instead?

解决方案

You need to pull the time difference apart into its constituent day, hour, minute and second elements, combine the number of days * 24 with the number of hours, and stick it back together.

When subtracting dates you get the difference as the number of days, so you need to convert the fractional part into the other elements, which you can do with a combination of trunc and mod; using a CTE to make this slightly easier to follow and showing each valu eon its own as well as combined into a single string:

with y as (
  select id, end_time - start_time as runtime
  from mytable
)
select id,
  runtime,
  trunc(runtime) as days,
  24 * trunc(runtime) as day_hours,
  trunc(24 * mod(runtime, 1)) as hours,
  trunc(60 * mod(24 * (runtime), 1)) as minutes,
  trunc(60 * mod(24 * 60 * (runtime), 1)) as seconds,
  lpad(24 * trunc(runtime)
    + trunc(24 * mod(runtime, 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * (runtime), 1)), 2, '0')
    ||':'|| lpad(trunc(60 * mod(24 * 60 * (runtime), 1)), 2, '0')
    as runtime
from y;

        ID    RUNTIME       DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ---------- ---------- ---------- ---------- ---------- ---------- --------
         1 .184918981          0          0          4         26         16 04:26:16 
         2 1.14465278          1         24          3         28         18 27:28:18 

You could also convert the dates to timestamps for the calculation, which gives you an interval type, and then use the extract function to get the elements instead; the principal is the same though:

with y as (
  select id,
    cast(end_time as timestamp) - cast (start_time as timestamp) as runtime
  from mytable
)
select id,
  runtime,
  extract (day from runtime) as days,
  24 * extract (day from runtime) as day_hours,
  extract (hour from runtime) as hours,
  extract (minute from runtime) as minutes,
  extract (second from runtime) as seconds,
  lpad(24 * extract (day from runtime) + extract (hour from runtime), 2, '0')
    ||':'|| lpad(extract (minute from runtime), 2, '0')
    ||':'|| lpad(extract (second from runtime), 2, '0')
    as runtime
from y;

        ID RUNTIME           DAYS  DAY_HOURS      HOURS    MINUTES    SECONDS RUNTIME 
---------- ----------- ---------- ---------- ---------- ---------- ---------- --------
         1 0 4:26:17.0          0          0          4         26         17 04:26:17 
         2 1 3:28:18.0          1         24          3         28         18 27:28:18 

Or a slight variation, getting the difference from the dates and then converting that to an interval:

with y as (
  select id,
    numtodsinterval(end_time - start_time, 'DAY') as runtime
  from mytable
)
...

SQL Fiddle demo.

这篇关于Oracle显示超过24小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！