Calculate difference between 2 date / times in Oracle SQL

Question

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

Near-duplicate: http://stackoverflow.com/questions/9322935/subtracting-dates-in-oracle-number-or-interval-datatype — , Dec 12 '14 at 16:53

score 137 · Answer 1 · edited Jun 30 '15 at 09:37

137

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.

edited Jun 30 '15 at 09:37

sleske

81,358
34
189
227

answered Jul 08 '09 at 08:44

Thilo

257,207
101
511
656

3

Has there been a change in Oracle's behavior at some point? When I subtract one date from another I get an `INTERVAL` data type, not a simple float. – Dec 12 '14 at 16:42
3

@JonofAllTrades: No, when you subtract values of type `DATE`, you get the number of days (as a `NUMBER`). However, if you subtract `TIMESTAMP` values, you get `INTERVALDS`. Probably you are working with `TIMESTAMP` and not `DATE` values. I edited the answer. – sleske Jun 30 '15 at 09:32
Regarding your note - Both the `DATE` and `TIMESTAMP` data types have a time (hours, minutes and seconds) component. `TIMESTAMP` also has a fractional seconds time component (and potentially time zone components). – MT0 Aug 23 '17 at 09:11
"This will give you the difference in days." Can you link to Oracle documentation please? – Roland Jan 28 '20 at 14:19

score 26 · Answer 2 · answered Oct 29 '15 at 18:30

26

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]

answered Oct 29 '15 at 18:30

Nikolay Frick

2,145
22
17

score 18 · Answer 3 · edited Nov 27 '14 at 09:31

18

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.

edited Nov 27 '14 at 09:31

Andrey Korneyev

26,353
15
70
71

answered Nov 27 '14 at 09:18

Ishan Rastogi

181
1
4

Mohsen Heydari · Answer 4 · 2015-06-30T13:41:23.880

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.

score 8 · Answer 5 · answered Jan 30 '15 at 09:27

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.

score 7 · Answer 6 · edited Oct 07 '12 at 13:01

7

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;

edited Oct 07 '12 at 13:01

stema

90,351
20
107
135

answered Oct 07 '12 at 12:56

SUNIL BEHERA

71
1
1

score 6 · Answer 7 · edited Jun 30 '15 at 13:42

6

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

edited Jun 30 '15 at 13:42

Mohsen Heydari

7,256
4
31
46

answered Jul 08 '09 at 09:24

HyLian

4,999
5
33
40

I have tried both the to_date and to_timestamp and both give me an answer in days, rounded down so if the difference is 1 hour I receive an answer of 0, multiplying this by 24 gives 0. I do receive the correct answer if I type in the date time but I can't do this for 25m rows. Any thoughts? – Steve Jul 08 '09 at 09:41
3

Using a substraction between timestamps it would return you another timestamp in a format like "DAYS HOUR:MINS:SECS.milisecs". You could trunc this to get the value you need – HyLian Jul 08 '09 at 09:57
7

Subtraction between timestamps returns an INTERVAL datatype. You can use the EXTRACT function to return various parts of an interval eg select extract(hour from (timestamp '2009-12-31 14:00:00' - timestamp '2009-12-31 12:15:00')) hr from dual; Note: That only shows the HOUR part, so if the difference is 1 day and 1 hour, this will show 1 not 25. – Gary Myers Jul 08 '09 at 22:42

score 4 · Answer 8 · edited Apr 16 '19 at 21:29

4

In oracle 11g

SELECT end_date - start_date AS day_diff FROM tablexxx
suppose the starT_date end_date is define in the tablexxx

edited Apr 16 '19 at 21:29

Rushabh Patel

2,672
13
34

answered Oct 07 '15 at 17:04

Michael

41
1

score 3 · Answer 9 · edited Apr 03 '14 at 06:46

3

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 );

edited Apr 03 '14 at 06:46

Tiny

27,221
105
339
599

answered Apr 03 '14 at 06:26

amitt Gaur

31
1

score 2 · Answer 10 · edited Aug 29 '16 at 16:08

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;

score 2 · Answer 11 · answered Jun 05 '18 at 08:08

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

score 1 · Answer 12 · answered Jul 28 '14 at 12:38

1

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

answered Jul 28 '14 at 12:38

user1012506 · Answer 13 · 2018-01-02T11:21:47.473

(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

score 1 · Answer 14 · edited Mar 21 '20 at 21:02

1

If You want get date defer from using table and column.

SELECT TO_DATE( TO_CHAR(COLUMN_NAME_1, 'YYYY-MM-DD'), 'YYYY-MM-DD') - 
       TO_DATE(TO_CHAR(COLUMN_NAME_2, 'YYYY-MM-DD') , 'YYYY-MM-DD')  AS DATEDIFF       
FROM TABLE_NAME;

edited Mar 21 '20 at 21:02

marc_s

732,580
175
1,330
1,459

answered Aug 21 '19 at 03:59

Nurullah

82
3

Why do you convert the dates to ISO strings only to immediately convert them back to dates? That's totally unnecessary. – Frank Schmitt Dec 08 '21 at 10:05

score 1 · Answer 15 · edited Nov 24 '19 at 10:08

1

select to_char(actual_start_date,'DD-MON-YYYY hh24:mi:ss') start_time,
to_char(actual_completion_date,'DD-MON-YYYY hh24:mi:ss') end_time,
floor((actual_completion_date-actual_start_date)*24*60)||'.'||round(mod((actual_completion_date-actual_start_date)*24*60*60,60)) diff_time
from fnd_concurrent_requests 
order by request_id desc;

edited Nov 24 '19 at 10:08

adiga

34,372
9
61
83

answered Nov 24 '19 at 10:02

user12423961

11
1

score 0 · Answer 16 · answered Sep 28 '13 at 06:30

0

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

answered Sep 28 '13 at 06:30

gpio

1
1

score 0 · Answer 17 · answered Feb 14 '14 at 09:09

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;

score 0 · Answer 18 · edited Nov 24 '19 at 10:08

0

Single query that will return time difference of two timestamp columns:

select INS_TS, MAIL_SENT_TS, extract( hour from (INS_TS - MAIL_SENT_TS) ) timeDiff 
from MAIL_NOTIFICATIONS;

edited Nov 24 '19 at 10:08

adiga

34,372
9
61
83

answered Jun 12 '19 at 17:10

Sandeep Raul

11
2

score 0 · Answer 19 · answered Feb 19 '21 at 05:04

0

select round( (tbl.Todate - tbl.fromDate) * 24 * 60 * 60 ) from table tbl

answered Feb 19 '21 at 05:04

Farrukh Saleem Sheikh

117
1
4

score 0 · Answer 20 · answered Nov 22 '22 at 13:16

0

for oracle sql I justbn did this and works perfect :

SELECT trunc(date_col_1) -  trunc(date_col_2) 
FROM   TABLE;

answered Nov 22 '22 at 13:16

Aymen Azoui

369
2
4

score -1 · Answer 21 · edited Feb 14 '12 at 08:36

-1

$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'";

edited Feb 14 '12 at 08:36

Dan D.

73,243
15
104
123

answered Aug 18 '09 at 11:21

Calculate difference between 2 date / times in Oracle SQL

21 Answers21

Linked

Related