Oracle SQL Hours Difference between dates in HH:MM

问题:

I already have a string to calculate the difference in hours between 2 dates which I'd got from stack:

24 *  (to_date(to_char(stp.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') -
(to_date(to_char(adhh.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))) diff_hours

But I want to see this as HH:MM:SS

Here's 2 dates/times as example: STP date 26-Feb-18 12.59.21 ADHH date 26-Feb-18 12.59.32

So I want it to say difference is 00:00:11 (11 seconds)

at the moment I get the result as -0.003 hours

Thanks in advance as always

回答1:

Assuming the columns are already DATE values (if not, I strongly recommend to change it) you can convert them to timestamp. It should be sufficient to convert only one value, but of course you can convert both.

CAST(stp.created_date AS TIMESTAMP) - adhh.created_date AS dif

Result is a INTERVAL DAY TO SECOND value which has fixed output format of DDD HH:MI:SS.FF. In order to get desired format you can use

REGEXP_SUBSTR(CAST(stp.created_date AS TIMESTAMP) - adhh.created_date, '\d{2}:\d{2}:\d{2}') AS dif

Note, TO_CHAR does not work for intervals. Thus you have to use REGEXP or extract components with EXTRACT(... FROM ...)

回答2:

Assuming the columns are already dates, and the therefore the conversion to and from strings you are doing is pointless, and that the difference is always going to be less than a day, you could do:

to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')

This gets the difference between the dates as a fraction of a day; makes sure it's positive via abs(); and then adds that fraction back on to a nominal date, which has its time as midnight. Then it converts that to a string, looking only at the time.

Quick demo:

-- CTEs to supply the two date/times
with stp (created_date) as (
  select cast(timestamp '2018-02-26 12:59:21' as date) from dual
),
adhh (created_date) as (
  select cast(timestamp '2018-02-26 12:59:32' as date) from dual
)
select to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')
from stp cross join adhh;

TO_CHAR(
--------
00:00:11

If the difference can exceed a day, but not a year, you could change the format model to something like 'FMDDD FMHH24:MI:SS', which will show full days at the start.

回答3:

You can convert it to an interval and then just use TO_CHAR to convert the interval to a string:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE STP ( id INT, created_date DATE );
CREATE TABLE ADHH ( id INT, created_date DATE );

INSERT INTO STP  values ( 1, TIMESTAMP '2018-02-26 12:59:21' );
INSERT INTO STP  values ( 2, TIMESTAMP '2018-02-26 12:00:30' );
INSERT INTO ADHH values ( 1, TIMESTAMP '2018-02-26 12:59:32' );
INSERT INTO ADHH values ( 2, TIMESTAMP '2018-02-26 12:00:00' );

Query 1:

SELECT TO_CHAR(
         ( s.created_date - a.created_date ) DAY(1) TO SECOND(0)
       ) AS diff
FROM   STP s
       INNER JOIN ADHH a
       ON ( s.id = a.id )

Results:

|        DIFF |
|-------------|
| -0 00:00:11 |
| +0 00:00:30 |

Query 2: If you do not have a days component (i.e. the dates are always less than 24 hours apart) then just replace the 0 substring:

SELECT REPLACE(
         ( s.created_date - a.created_date ) DAY(1) TO SECOND(0),
         '0 '
       ) AS diff
FROM   STP s
       INNER JOIN ADHH a
       ON ( s.id = a.id )

Results:

|      DIFF |
|-----------|
| -00:00:11 |
| +00:00:30 |

回答4:

I will suggest you small change in your query

SELECT 
TO_CHAR(TRUNC(x/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(x,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(x,60),'FM00')
 FROM DUAL   

just replace 'x' in above query with your result column

24 *  (
to_date(to_char(stp.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') -
(to_date(to_char(adhh.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DDhh24:mi:ss')))

hope this helps..

回答5:

It looks like your columns are already in DATE format (they should be!). In that case you can just get the difference between the dates as a DAY TO SECOND INTERVAL. Use the NUMTODSINTERVAL function for that. Then using the EXTRACT function, you can get the interval in terms of hours, minutes and seconds.

Here is a sample query.

WITH
    stp (created_date)
    AS
        (SELECT CAST (TIMESTAMP '2018-02-26 12:59:21' AS DATE) FROM DUAL),
    adhh (created_date)
    AS
        (SELECT CAST (TIMESTAMP '2018-02-26 12:59:32' AS DATE) FROM DUAL)
SELECT    LPAD (EXTRACT (HOUR FROM diff_int), 2, '0')   -- get hours from interval
       || ':'
       || LPAD (EXTRACT (MINUTE FROM diff_int), 2, '0') -- get minutes from interval
       || ':'
       || LPAD (EXTRACT (SECOND FROM diff_int), 2, '0') -- get seconds from interval
           AS diff_hours
FROM (SELECT NUMTODSINTERVAL (  -- get difference between the dates as an interval
                 b.created_date
                 - a.created_date,
                 'DAY'
             )
                 diff_int
      FROM stp a,
           adhh b);

Here is the result.

DIFF_HOURS

00:00:11