许多天之间易于阅读的经过时间

Question

许多天之间易于阅读的经过时间

6

我使用Oracle SQL工作，有一个包含3列的表：Process，Start Date和End Date。我想要计算每个过程的时间持续时间。

我使用了以下查询：

select 
(enddate.date_value - startdate.date_value) as duration
from dual

"结果是以天为单位。

例如：开始日期是30.3.2016 17:14:53，结束日期是8.7.2016 14:51:21 当我使用查询时，结果是99.90032407407407407407407407407407407407，但我想要像这样的结果： 3个月、7天、21小时、36分钟、28秒。

我该怎么做呢？"

- Nikolay Ivanov

一旦你需要比天更长的时间单位，就会遇到问题。你必须选择是撒谎并拥有标准长度的月份（例如所有30天），还是有一个情况，“3个月，7天，…”可以表示不同的实际时间段，具体取决于这段时间发生的时间。两者都不是特别吸引人的。你确定你真的需要你在这里要求的东西，并且那些要求它的人了解这些权衡吗？ - Damien_The_Unbeliever

@Damien_The_Unbeliever，我同意你的观点，如果你只想转换几天，但如果你想在两个日期之间表示一个持续时间，你不需要每个月固定数量的天数（例如所有30天）或每年固定数量的天数，看看我的建议（我相信我们可以简化/优化它，但它似乎有效！） - Indent

@Indent - 我想表达的是，如果您不采用固定长度的月份，"3个月，7天，..."可能代表不同的时间段。即它是2000年2月1日至5月8日之间的期间，表示实际的97天。一年后，在同样的日期之间，它代表96天。在OPs的例子中，它代表99天。 - Damien_The_Unbeliever

请阅读我的回答，我从未在查询中采用固定的月份数，并且找到了预期的结果。在OP的示例中，它代表了30.3.2016 17:14:53和8.7.2016 14:51:21之间的持续时间。类似于https://momentjs.com/docs/#/plugins/preciserange/。 - Indent

我已经使用 moment.js 的示例添加了测试，我的代码产生了相同的结果。 - Indent

3个回答

1

两个 DATE 值之间的差异是表示天数的 数字。您似乎想要一个 间隔，可以使用 TIMESTAMP 值来实现。

select cast(enddate as timestamp) - cast(startdate as timestamp)
from the_table

从一个时间戳减去另一个时间戳的结果是一个时间间隔。

时间间隔值的格式化在Oracle中非常棘手。例如，参见使用to_char格式化时间间隔。

- user330315

实际上只需要转换一个，无论是 enddate 还是 startdate。 - Wernfried Domscheit

1

根据我之前的回答，您可以创建一个名为sinceHumanReadable的Oracle函数：

例如，来自https://momentjs.com/docs/#/plugins/preciserange/的示例产生相同的结果。

moment("2014-01-01 12:00:00").preciseDiff("2015-03-04 16:05:06");
// 1 year 2 months 3 days 4 hours 5 minutes 6 seconds

http://sqlfiddle.com/#!4/d6783/1

create or replace FUNCTION sinceHumanReadable(start_date IN date,end_date IN date) 
    RETURN VARCHAR2
IS result VARCHAR2(255);

    BEGIN 

    with 
    dates_parts as (
    SELECT
        EXTRACT(YEAR   FROM (end_date - start_date) YEAR TO MONTH ) as Year,
        EXTRACT(MONTH  FROM (end_date - start_date) YEAR TO MONTH ) as Month,       
        EXTRACT(HOUR   FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Hour,
        EXTRACT(MINUTE FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Minute,    
        EXTRACT(SECOND FROM cast(end_date as timestamp) - cast(start_date as timestamp)) as Second
    FROM dual
    ),
    dates_parts_with_day as (
    select        
        Year,Month,Hour,Minute,Second,
        EXTRACT(DAY FROM (end_date - ADD_MONTHS(start_date,Month+Year*12)) DAY TO SECOND ) as Day
    from dates_parts
    )
    select
        decode(dates_parts_with_day.Year, 0,'', dates_parts_with_day.Year  || ' years ' )||
        decode(dates_parts_with_day.Month,0,'', dates_parts_with_day.Month || ' months ')||
        decode(dates_parts_with_day.Day,0,'', dates_parts_with_day.Day || ' days ')||
        decode(dates_parts_with_day.Hour,0,'', dates_parts_with_day.Hour || ' hours ')||
        decode(dates_parts_with_day.Minute,0,'', dates_parts_with_day.Minute || ' minutes ')||
        dates_parts_with_day.Second || ' seconds'
    into result
    from 
        dates_parts_with_day;

    RETURN(result); 

    END sinceHumanReadable;
GO

查询

with dates as (    
  select sysdate-99.90032407407407407407407407407407407407 as d1,sysdate  as d2 from dual
  union all
  select to_date('2016-03-30 17:14:53','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2016-07-08 14:51:21','yyyy-mm-dd hh24:mi:ss')   as d2 from dual
  union all
  select to_date('2014-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss') as d1,to_date('2015-03-04 16:05:06','yyyy-mm-dd hh24:mi:ss')   as d2 from dual  
  union all
  select sysdate as d1,add_months(sysdate,35)   as d2 from dual
  union all
  select sysdate as d1,sysdate as d2 from dual
)
select
    d1,d2,
    sinceHumanReadable(d1,d2) as since
from
    dates;

将会产生以下内容：

|                   D1 |                   D2 |                                               SINCE |
|----------------------|----------------------|-----------------------------------------------------|
| 2017-07-19T17:50:00Z | 2017-10-27T15:26:28Z |      3 months 7 days 21 hours 36 minutes 28 seconds |
| 2016-03-30T17:14:53Z | 2016-07-08T14:51:21Z |      3 months 7 days 21 hours 36 minutes 28 seconds |
| 2014-01-01T12:00:00Z | 2015-03-04T16:05:06Z | 1 years 2 months 3 days 4 hours 5 minutes 6 seconds |
| 2017-10-27T15:26:28Z | 2020-09-27T15:26:28Z |                         2 years 11 months 0 seconds |
| 2017-10-27T15:26:28Z | 2017-10-27T15:26:28Z |                                           0 seconds |

- Indent

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Indent · Accepted Answer

这个（特别是对于DAY的）复杂查询：

为了计算出正确的“Day”，我将“Month”和12 *“Year”添加到原始日期中。

with dates as(
select
   sysdate       as d1,
   sysdate-99.90032407407407407407407407407407407407-365  as d2
from
   dual
),
dates_parts as (
SELECT
    d1,
    d2,
    EXTRACT(YEAR   FROM (d1 - d2) YEAR TO MONTH ) as Year,
    EXTRACT(MONTH  FROM (d1 - d2) YEAR TO MONTH ) as Month,
    EXTRACT(DAY    FROM (d1 - d2) DAY TO SECOND ) as Day,
    EXTRACT(HOUR   FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Hour,
    EXTRACT(MINUTE FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Minute,    
    EXTRACT(SECOND FROM cast(d1 as timestamp) - cast(d2 as timestamp)) as Second
FROM dates
)
select
    dates_parts.Year,
    dates_parts.Month,
    dates_parts.Day,
    dates_parts.Hour,
    dates_parts.Minute,
    dates_parts.Second,
    EXTRACT(DAY FROM (d1 - ADD_MONTHS(d2,Month+Year*12)) DAY TO SECOND ) as Day_Corrected
from 
    dates_parts

将产生不同的日期部分：

| YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | DAY_CORRECTED |
|------|-------|-----|------|--------|--------|---------------|
|    1 |     3 | 464 |   21 |     36 |     28 |             7 |