sql - 将十进制 DAYS 转换为 TIME 格式

Question

你好，我是 sql 的新手，我需要你的帮助。

我有下表:

    start_date      |     end_date          
02.08.2012 09:27:19 |  06.08.2012 07:53:00  
06.08.2012 06:58:58 |  06.08.2012 13:42:33
05.06.2012 14:35:58 |  05.06.2012 14:42:31

我需要显示 start_date 和 end_date 之间的持续时间。

我是这样做的: 结束日期 - 开始日期 = 持续时间
但它显示一个十进制值(见下表 - 字段持续时间)。

我需要像这样显示字段持续时间:HH:MM:SS - 不是十进制。

    start_date      |     end_date         |         duration        | I need it like this
02.08.2012 09:27:19 |  06.08.2012 07:53:00 | --> 3.93450231481481    |    94:25:41
06.08.2012 06:58:58 |  06.08.2012 13:42:33 | --> 0.280266203703704   |    06:43:35
05.06.2012 14:35:58 |  05.06.2012 14:42:31 | --> 0.0045486111111...  |    00:06:33

如果有什么不确定的，我会尝试解释。
我希望你能帮助我。祝你今天过得愉快。

Answer 1

日期的减法为您提供天数。你可以把它们变成 INTERVAL DAY TO SECOND值使用 NumToDSInterval 功能。

开始日期 02.08.2012 09:27:19和结束日期 06.08.2012 07:53:00 ，结果很接近，但不是你想要的(并注意浮点型舍入，其中 41 秒变为 40.9999999...):

SQL> SELECT NumToDSInterval(
  2    TO_DATE('06.08.2012 07:53:00', 'DD.MM.YYYY HH24:MI:SS') -
  3    TO_DATE('02.08.2012 09:27:19', 'DD.MM.YYYY HH24:MI:SS'), 'DAY') AS Elapsed
  4  FROM DUAL;

ELAPSED
-----------------------------
+000000003 22:25:40.999999999

但这是一个很好的起点，因为一旦耗时在 INTERVAL类型你可以 EXTRACT 天、小时、分钟和秒。我会这样做:

WITH spans AS (
  SELECT NUMTODSINTERVAL(end_date - start_date, 'DAY') AS Elapsed
  FROM myTable
)
SELECT
  EXTRACT(DAY FROM Elapsed) * 24 + EXTRACT(HOUR FROM Elapsed) || ':' ||
  EXTRACT(MINUTE FROM Elapsed) || ':' ||
  ROUND(EXTRACT(SECOND FROM Elapsed), 0) AS duration
FROM spans

我用你的第一组日期试过这个，效果很好； ROUND使秒数正确显示为 41。

附录 OP 需要在 View 中使用此逻辑，我很确定 CTE(公用表表达式，否则称为“WITH foo AS(查询)”)不会为 View 而飞行。

要在 View 中使用它，请将 CTE 移至子查询:

CREATE OR REPLACE VIEW myView AS
  SELECT
    EXTRACT(DAY FROM Elapsed) * 24 + EXTRACT(HOUR FROM Elapsed) || ':' ||
    EXTRACT(MINUTE FROM Elapsed) || ':' ||
    ROUND(EXTRACT(SECOND FROM Elapsed), 0) AS duration
  FROM (
    SELECT NUMTODSINTERVAL(end_date - start_date, 'DAY') AS Elapsed
    FROM myTable
  )