Calculate difference between 2 date / times in Ora

问题:

I have a table as follows:

Filename - varchar
Creation Date - Date format dd/mm/yyyy hh24:mi:ss
Oldest cdr date - Date format dd/mm/yyyy hh24:mi:ss

How can I calcuate the difference in hours minutes and seconds (and possibly days) between the two dates in Oracle SQL?

Thanks

回答1:

You can substract dates in Oracle. This will give you the difference in days. Multiply by 24 to get hours, and so on.

SQL> select oldest - creation from my_table;

If your date is stored as character data, you have to convert it to a date type first.

SQL> select 24 * (to_date(\'2009-07-07 22:00\', \'YYYY-MM-DD hh24:mi\') 
             - to_date(\'2009-07-07 19:30\', \'YYYY-MM-DD hh24:mi\')) diff_hours 
       from dual;

DIFF_HOURS
----------
       2.5

---

Note:

This answer applies to dates represented by the Oracle data type DATE. Oracle also has a data type TIMESTAMP, which can also represent a date (with time). If you subtract TIMESTAMP values, you get an INTERVAL; to extract numeric values, use the EXTRACT function.

回答2:

declare
strTime1 varchar2(50) := \'02/08/2013 01:09:42 PM\';
strTime2 varchar2(50) := \'02/08/2013 11:09:00 PM\';
v_date1 date := to_date(strTime1,\'DD/MM/YYYY HH:MI:SS PM\');
v_date2 date := to_date(strTime2,\'DD/MM/YYYY HH:MI:SS PM\');
difrence_In_Hours number;
difrence_In_minutes number;
difrence_In_seconds number;
begin
    difrence_In_Hours   := (v_date2 - v_date1) * 24;
    difrence_In_minutes := difrence_In_Hours * 60;
    difrence_In_seconds := difrence_In_minutes * 60;

    dbms_output.put_line(strTime1);        
    dbms_output.put_line(strTime2);
    dbms_output.put_line(\'*******\');
    dbms_output.put_line(\'difrence_In_Hours  : \' || difrence_In_Hours);
    dbms_output.put_line(\'difrence_In_minutes: \' || difrence_In_minutes);
    dbms_output.put_line(\'difrence_In_seconds: \' || difrence_In_seconds);        
end ;

Hope this helps.

回答3:

To get result in seconds:

select (END_DT - START_DT)*60*60*24 from MY_TABLE;

Check [https://community.oracle.com/thread/2145099?tstart=0][1]

回答4:

select 
    extract( day from diff ) Days, 
    extract( hour from diff ) Hours, 
    extract( minute from diff ) Minutes 
from (
        select (CAST(creationdate as timestamp) - CAST(oldcreationdate as timestamp)) diff   
        from [TableName] 
     );

This will give you three columns as Days, Hours and Minutes.

回答5:

You could use to_timestamp function to convert the dates to timestamps and perform a substract operation.

Something like:

SELECT 
TO_TIMESTAMP (\'13.10.1990 00:00:00\',\'DD.MM.YYYY HH24:MI:SS\')  - 
TO_TIMESTAMP (\'01.01.1990:00:10:00\',\'DD.MM.YYYY:HH24:MI:SS\')
FROM DUAL

回答6:

Calculate age from HIREDATE to system date of your computer

SELECT HIREDATE||\'        \'||SYSDATE||\'       \' ||
TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12) ||\' YEARS \'||
TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE))-(TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)/12)*12))||
\'MONTHS\' AS \"AGE  \"  FROM EMP;

回答7:

You may also try this:

select to_char(to_date(\'1970-01-01 00:00:00\', \'yyyy-mm-dd hh24:mi:ss\')+(end_date - start_date),\'hh24:mi:ss\')
       as run_time from some_table;

It displays time in more human readable form, like: 00:01:34. If you need also days you may simply add DD to last formatting string.

回答8:

select days||\' \'|| time from (
SELECT to_number( to_char(to_date(\'1\',\'J\') +
    (CLOSED_DATE - CREATED_DATE), \'J\') - 1)  days,
   to_char(to_date(\'00:00:00\',\'HH24:MI:SS\') +
      (CLOSED_DATE - CREATED_DATE), \'HH24:MI:SS\') time
 FROM  request  where REQUEST_ID=158761088 );

回答9:

select (floor(((DATE2-DATE1)*24*60*60)/3600)|| \' : \' ||floor((((DATE2-DATE1)*24*60*60) -floor(((DATE2-DATE1)*24*60*60)/3600)*3600)/60)|| \'  \' ) as time_difference from TABLE1 

回答10:

If you want something that looks a bit simpler, try this for finding events in a table which occurred in the past 1 minute:

With this entry you can fiddle with the decimal values till you get the minute value that you want. The value .0007 happens to be 1 minute as far as the sysdate significant digits are concerned. You can use multiples of that to get any other value that you want:

select (sysdate - (sysdate - .0007)) * 1440 from dual;

Result is 1 (minute)

Then it is a simple matter to check for

select * from my_table where (sysdate - transdate) < .00071;

回答11:

(TO_DATE(:P_comapre_date_1, \'dd-mm-yyyy hh24:mi\') - TO_DATE(:P_comapre_date_2, \'dd-mm-yyyy hh24:mi\'))*60*60*24 sum_seconds,
         (TO_DATE(:P_comapre_date_1, \'dd-mm-yyyy hh24:mi\') - TO_DATE(:P_comapre_date_2, \'dd-mm-yyyy hh24:mi\'))*60*24 sum_minutes,
         (TO_DATE(:P_comapre_date_1, \'dd-mm-yyyy hh24:mi\') - TO_DATE(:P_comapre_date_2, \'dd-mm-yyyy hh24:mi\'))*24 sum_hours,
         (TO_DATE(:P_comapre_date_1, \'dd-mm-yyyy hh24:mi\') - TO_DATE(:P_comapre_date_2, \'dd-mm-yyyy hh24:mi\')) sum_days 

回答12:

$sql=\"select bsp_bp,user_name,status,
to_char(ins_date,\'dd/mm/yyyy hh12:mi:ss AM\'),
to_char(pickup_date,\'dd/mm/yyyy hh12:mi:ss AM\'),
trunc((pickup_date-ins_date)*24*60*60,2),message,status_message 
from valid_bsp_req where id >= \'$id\'\"; 

回答13:

This will count time between to dates:

SELECT
  (TO_CHAR( TRUNC (ROUND(((sysdate+1) - sysdate)*24,2))*60,\'999999\')
  +
  TO_CHAR(((((sysdate+1)-sysdate)*24)- TRUNC(ROUND(((sysdate+1) - sysdate)*24,2)))/100*60 *100, \'09\'))/60
FROM dual

回答14:

Here\'s another option:

with tbl_demo AS
    (SELECT TO_DATE(\'11/26/2013 13:18:50\', \'MM/DD/YYYY HH24:MI:SS\') dt1
   , TO_DATE(\'11/28/2013 21:59:12\', \'MM/DD/YYYY HH24:MI:SS\') dt2 
     FROM dual)
SELECT dt1
     , dt2
     , round(dt2 - dt1,2) diff_days
     , round(dt2 - dt1,2)*24 diff_hrs
     , numtodsinterval((dt2 - dt1),\'day\') diff_dd_hh_mm_ss
  from tbl_demo;

回答15:

in oracle 11g

select end_date - start_date as day_diff from tablexxx suppose the starT_date end_date is define in the tablexxx

回答16:

If you select two dates from \'your_table\' and want too see the result as a single column output (eg. \'days - hh:mm:ss\') you could use something like this. First you could calculate the interval between these two dates and after that export all the data you need from that interval:

         select     extract (day from numtodsinterval (second_date
                                                   - add_months (created_date,
                                                                 floor (months_between (second_date,created_date))),
                                                   \'day\'))
             || \' days - \'
             || extract (hour from numtodsinterval (second_date
                                                    - add_months (created_date,
                                                                  floor (months_between (second_date,created_date))),
                                                    \'day\'))
             || \':\'
             || extract (minute from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      \'day\'))
             || \':\'
             || extract (second from numtodsinterval (second_date
                                                      - add_months (created_date,
                                                                    floor (months_between (second_date, created_date))),
                                                      \'day\'))
     from    your_table

And that should give you result like this: 0 days - 1:14:55