oracle中如何减去2个日期以获得小时和分钟的结果

Question

我想减去 2 个日期，并以一位小数表示小时和分钟的结果。

我有下表，我正在这样做，但结果不符合预期。

有一些细微的变化，我确信这是简单的算术，但我没有得到正确的结果。

select start_time, end_time, (end_time-start_time)*24 from 
come_leav;    

START_TIME          END_TIME            (END_TIME-START_TIME)*24    
------------------- ------------------- ------------------------      
21-06-2011 14:00:00 21-06-2011 16:55:00  2.9166667      
21-06-2011 07:00:00 21-06-2011 16:50:00  9.8333333      
21-06-2011 07:20:00 21-06-2011 16:30:00  9.1666667      

我想要如下结果（结束时间-开始时间）。

16:55- 14:00 = 2.55      
16:50-07:00 = 9.5      
16:30-7:20 = 9.1 and so on.    

我怎样才能做到这一点？

Answer 1

SQL> edit
Wrote file afiedt.buf

  1  select start_date
  2      , end_date
  3      , (24 * extract(day from (end_date - start_date) day(9) to second))
  4          + extract(hour from (end_date - start_date) day(9) to second)
  5          + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR.MINUTE"
  6* from t
SQL> /

START_DATE          END_DATE            HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00        2.55
21-06-2011 07:00:00 21-06-2011 16:50:00         9.5
21-06-2011 07:20:00 21-06-2011 16:30:00         9.1

对于遇到此代码的人来说，应该注意的是，小数部分是实际的分钟差异，而不是小时的一部分。因此，.5 代表 50 分钟，而不是 30 分钟。

Answer 2

试试这个

round(to_number(end_time - start_time) * 24)

Answer 3

Oracle 将日期表示为天数，因此 (end_time-start_time)*24 为您提供小时数。假设您在 h 列中有此数字（例如 2.9166667）。然后您可以轻松地将其转换为您想要的格式：FLOOR(h) + (h-FLOOR(h))/100*60。

示例：

WITH diff AS (
    SELECT (TO_DATE('21-06-2011 16:55:00', 'DD-MM-YYYY HH24:MI:SS') - TO_DATE('21-06-2011 14:00:00', 'DD-MM-YYYY HH24:MI:SS'))*24 h
    FROM dual
) SELECT FLOOR(h) + (h-FLOOR(h))/100*60
FROM diff

就您而言：

SELECT start_time, end_time,
    FLOOR((end_time-start_time)*24) + ((end_time-start_time)*24-FLOOR((end_time-start_time)*24))/100*60 AS hours_diff
FROM come_leav 

Answer 4

将时差添加到一天的开始，然后以 24 小时格式从中提取小时、分钟和秒。

注意：注意天数！

示例

SQL

    with w_dates as (
      select to_date('08/08/2023 01:00:00', 'DD/MM/YYYY HH24:MI:SS') date_start, 
             to_date('08/08/2023 02:23:45', 'DD/MM/YYYY HH24:MI:SS') date_end from dual)
    select floor(date_end-date_start) days, 
           to_char(trunc(sysdate) + sum(date_end-date_start),'HH24:MI:SS') hours_minutes_seconds 
      from w_dates;

结果

DAYS | HOURS_MINUTES_SECONDS
-----------------------------
   0 | 01:23:45

Answer 5

试试这个：

    SELECT
    TRIM(TO_CHAR(TRUNC(((86400*(end_time - start_time))/60)/60)-24*(trunc((((86400*(end_time - start_time))/60)/60)/24)),'00')) ||'.'||
    TRIM(TO_CHAR(TRUNC((86400*(end_time - start_time))/60)-60*(trunc(((86400*(end_time - start_time))/60)/60)),'00')) ||'.'  as duration
FROM come_leav;

Answer 6

编辑：如果您需要一个数字，那么

    trunc(end_date-start_date)*24+
    to_number(to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI'))

对于字符串结果，如果增量小于 24H：
我会选择

    to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI')

或 ...'HH24:MI:SS'，但这是我个人的偏好。

对于超过 24 小时的术语，我会在前面加上

    trunc(end_date-start_date)||"days "||
    to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI')

“是”，因为预言机以天为单位进行计数，精度为秒，您正在处理算术问题。一次，因为您只处理分钟（因此您可能将数字舍入为 trunc(days*24*60+0.5)/24/60），但数字 1/24/ 上的二进制算术不精确60仍然可能会给你带来麻烦。

Edit2.1：

    to_char(24*(trunc(end_date)-trunc(start_date))+to_number(to_char(end_date,'HH24.MI'))-to_number(to_char(start_date,'HH24.MI')),'99999.99')

但是对于平均值来说，结果可能会非常令人困惑，因为小数 7.50 表示七个半小时，或至少 7 小时 50 分钟，而不是 7 小时 10 分钟的经过时间。

Answer 7

这个查询对我来说非常有用，如果任何人想要开始日期和结束日期之间的差异以及像 HH:MI:SS 这样的时间，请使用这个查询。

SELECT
    TRIM(TO_CHAR(TRUNC(((86400*(end_date - start_date))/60)/60)-24*(trunc((((86400(end_date - start_date))/60)/60)/24)),'00')) ||'.'||
    TRIM(TO_CHAR(TRUNC((86400*(actual_completion_date - actual_start_date))/60)-60*(trunc(((86400*(end_date - start_date))/60)/60)),'00')) ||'.'||
    TRIM(TO_CHAR(TRUNC((86400*(end_date - start_date)))-60*(trunc((86400*(end_date - actual_start_date))/60)),'00')) as duration  
FROM fnd_concurrent_requests; 

Answer 8

这是一种非常丑陋的方法，第一部分并没有完全由OP提出问题，但它提供了一种通过减去2个日期字段来获取结果的方法——在我的例子中，是CREATED_DATE 和今天由 SYSDATE 表示：

SELECT FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12) || ' years, '  
|| (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) - 
   (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12)) * 12) || ' months, '  
-- we take total days - years(as days) - months(as days) to get remaining days
|| FLOOR((SYSDATE - CREATED_DATE) -      -- total days
   (FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) -      -- years, as days
   -- this is total months - years (as months), to get number of months, 
   -- then multiplied by 30.416667 to get months as days (and remove it from total days)
   FLOOR(FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12)))  
|| ' days, '   
-- Here, we can just get the remainder decimal from total days minus 
-- floored total days and multiply by 24       
|| FLOOR(
     ((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
   )
|| ' hours, ' 
-- Minutes just use the unfloored hours equation minus floored hours, 
-- then multiply by 60
|| ROUND(
       (
         (
           ((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
         ) - 
         FLOOR((((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24))
       )*60
    )
|| ' minutes'  
AS AGE FROM MyTable`

它提供 x 年、x 月、x 天、x 小时、x 分钟的输出。您可以通过更改连接的字符串来重新格式化它。

为了更直接地回答这个问题，我继续写了如何获取以分钟为单位的总小时数 hours.mines：

select  
((FLOOR(end_date - start_date))*24)
|| '.' ||
ROUND(
       (
         (
           ((end_date - start_date)-(FLOOR(end_date - start_date)))*24
         ) - 
         FLOOR((((end_date - start_date)-(FLOOR(end_date - start_date)))*24))
       )*60
    )
from 
come_leav;   

Answer 9

您可以使用提取物：

SELECT start_time, end_time, extract(HOUR FROM end_time-start_time) || '.' || extract(MINUTE FROM end_time-start_time)  
From come_leav;