How to subtract 2 dates in oracle to get the result in hour and minute

Question

I want to subtract 2 dates and represent the result in hour and minute in one decimal figure.

I have the following table and I am doing it in this way but the result is not as desired.

There is some slight variation, I'm sure this is simple arithmetic but I'm not getting it right.

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

I want the result (end_time-start_time) as below.

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

How can I do that?

Expected result looks rather ridiculous. It should be like this. 16:55- 14:00 = 2:55 or 2.92 16:50-07:00 = 9:50 or 9.83 16:30-7:20 = 9:10 or 9.17 — Thinhbk, Sep 19 '11 at 01:36
will i be able to get it in the form of 2.55. I am actually reporting time in and time out and calculating the differnce in hours and minutes. thanks. — Macky, Sep 19 '11 at 06:08
I have to say, this is probably one of most well-written SQL questions I have seen on Stack Overflow. I know you've probably long-forgotten this, but I had to say it. — user5670895, Jan 22 '16 at 20:15

Shannon Severance · Answer 1 · 2016-12-06T16:30:11.783

4

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

It should be noted for those coming across this code that the decimal portions are ACTUAL minute differences, and not part of an hour. .5, therefore, represents 50 minutes, not 30 minutes.

edited Dec 06 '16 at 16:30

answered Sep 18 '11 at 16:26

Shannon Severance

18,025
3
46
67

Below is the result when i run this. But pls have a look at the last line it gives me 7.1 where as I need it to be 7.5 (14-6.50) without jeoparizing other figures. Appreciate your help START_TIME END_TIME 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 22-06-2011 07:20:00 22-06-2011 16:30:00 9.1 23-06-2011 07:20:00 23-06-2011 16:30:00 9.1 21-06-2011 06:50:00 21-06-2011 14:00:00 7.1 – Macky Sep 19 '11 at 12:12
1

@Macky. That makes no sense. You asked for subtraction of time that would result in `hours.minutes`. (Which doesn't make much sense to start with.) 14:00 minus 6:50 is seven hours ten minutes or 7.1. Are you saying that you want 2:00 - 1:59 to be 0.41 for a one minute and 1:41 - 1:00 to be 0.41 for forty one minutes? – Shannon Severance Sep 19 '11 at 16:43
@ShannonSeverance Great job deciphering what he wanted and the code above. As a note on the code in the post, because of the OP's odd requirement, it should be noted the decimal depicts actual minutes, not fractional parts of an hour. I submitted an edit. – vapcguy Dec 06 '16 at 16:08

score 2 · Answer 2 · answered Sep 18 '11 at 08:53

2

Try this

round(to_number(end_time - start_time) * 24)

answered Sep 18 '11 at 08:53

reader_1000

2,473
17
15

Thanks for your help. But the result is not what I wanted. See below SQL> select to_number(end_time-start_time)*24 from come_leav; TO_NUMBER(END_TIME-START_TIME)*24 --------------------------------- 2.9166667 9.8333333 9.1666667 9.1666667 9.1666667 7.1666667 – Macky Sep 18 '11 at 09:52
I don't have an oracle database installed, therefore I couldn't checked the query I gave. Sorry for incorrect/incomplete answer. – reader_1000 Sep 18 '11 at 12:12
It's accurate in the sense that it gives the decimal equivalent of the minutes -- just have to multiply that portion by 60. Problem is the odd requirement where the OP needed it as `HH.MM`. – vapcguy Dec 06 '16 at 18:52
`FLOOR((end_time - start_time) * 24) || '.' || ROUND(((end_time - start_time) * 24) - FLOOR((end_time - start_time) * 24)*60)` is likely how the OP would need to use it. – vapcguy Dec 06 '16 at 19:54

score 2 · Answer 3 · answered Sep 18 '11 at 12:02

Oracle represents dates as a number of days, so (end_time-start_time)*24 gives you hours. Let's assume you have this number (eg. 2.9166667) in h column. Then you can easily convert it to the format you want with: FLOOR(h) + (h-FLOOR(h))/100*60.

Example:

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

In your case:

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

score 1 · Answer 4 · answered Aug 08 '23 at 08:07

Add the time difference to beginning of the day then extract hours, minutes and seconds out of it in 24 hour format.

NOTE: be aware of days!

Example

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;

Result

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

score 0 · Answer 5 · edited Apr 09 '13 at 12:37

This query is very usefull for me and if any body want diffrence between start_date and end_date with time like HH:MI:SS please use this query.

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;

vapcguy · Answer 6 · 2016-12-08T20:56:47.083

This is a very ugly way to do it, and this first part doesn't exactly question by the OP, but it gives a way to get results by subtracting 2 date fields -- in my case, the CREATED_DATE and today represented by 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`

It delivers the output as x years, x months, x days, x hours, x minutes. It could be reformatted however you like by changing the concatenated strings.

To more directly answer the question, I've gone ahead and written out how to get the total hours with minutes as hours.minutes:

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;

Kevin Burton · Answer 7 · 2011-09-19T09:38:05.917

0

try this:

    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;

edited Sep 19 '11 at 09:38

answered Sep 18 '11 at 08:15

Kevin Burton

11,676
2
24
37

Thanks for your help, I am getting the results as desired but cant i get it without concatenation, I mean as a final result because I have to use this to calculate difference in time in and time out. – Macky Sep 18 '11 at 09:51
@Macky if you have to use it to calculate stuff, the results in your original post are correct. 1h30 is one hour and a *half*, a half is 0.5, so 1h30 = 1.5 hours. – roselan Sep 18 '11 at 12:11
Hi roslan, you are right but I want the calculation to be done as I requested b'coz I want to find the time difference between start time and end time. So I want the result as requested earlier i.e 16:55-14:00 to be 2.55 and not 2.916667. so please advise a simpler way to do it thankx... – Macky Sep 18 '11 at 12:36
@Macky, I have edited the post... replaced the : seperator with a . and removed the seconds from the string. – Kevin Burton Sep 19 '11 at 09:40

vmatyi · Answer 8 · 2011-09-21T00:47:00.023

0

Edit: if you need a number, then

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

For string result, if delta is LESS THAN 24H: I would go with

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

or ...'HH24:MI:SS', but thats my personal preference.

for longer than 24H terms, I would prefix with

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

Yes, as oracle counts in days, with seconds precision, you are dealing with arithmetical problems. Once because you are only handling minutes (so you might round your number to trunc(days*24*60+0.5)/24/60), but the binary arithmetic imprecision on the number 1/24/60 might still cause you troubles.

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

But The result could be quite confusing for the average, as the decimal 7.50 would suggest seven and a half hour, or at least 7 hour 50 minutes, opposed to the elapsed time of 7 hours 10 minutes.

edited Sep 21 '11 at 00:47

answered Sep 18 '11 at 19:14

vmatyi

1,273
10
21

select start_time, end_time, to_char(trunc(sysdate)+(end_time-start_time),'HH24.MI') from come_leav; S_TIME E_TIME T_C 21-06-2011 14:00:00 21-06-2011 16:55:00 02.55 21-06-2011 07:00:00 21-06-2011 16:50:00 09.50 21-06-2011 07:20:00 21-06-2011 16:30:00 09.10 22-06-2011 07:20:00 22-06-2011 16:30:00 09.10 23-06-2011 07:20:00 23-06-2011 16:30:00 09.10 21-06-2011 06:50:00 21-06-2011 14:00:00 07.10. Please have a look at the last result (14-06.50) returns 07.10 where as I want it as (14-06.50) = 7.5. Any suggestion how to overcome that.. Thanks a million for your help. – Macky Sep 19 '11 at 05:44
This is what i get, Seems to have fixed the last one but pls have a look at the first one it gives -88.45 instead of 2.55. Pls help I need to get it right. Appreciate.. START_TIME END_TIME --------- ------------------- TRUNC(END_TIME-START_TIME)+TO_CHAR(TO_NUMBER(TO_CHAR(END_TIME,'HH24.MI'))-TO_NUMBER(TO_CHAR(START_TI -------------------------------------------------------------------- 21-09-2011 14:00:00 21-06-2011 16:55:00 -88.45 21-06-2011 07:00:00 21-06-2011 16:50:00 9.5 23-06-2011 07:20:00 23-06-2011 16:30:00 9.1 21-06-2011 06:50:00 21-06-2011 14:00:00 7.5 – Macky Sep 19 '11 at 10:41
Ok, made some mistake, 2.1 now works as your will. But couldnt get 2.55 as they are on different months, and end is smaller than start, so got to result in a big negative number. (If you remove the 24*(...)+ part, you'll get 2.55, but you shouldn't) – vmatyi Sep 21 '11 at 00:53
Hi @vmatyi, Sorry. Yes that date is on a different month. It has to be on the same month or I should say it will be on a same date. I am finding the difference by subtracting time_out from time_in to get in hr.min. This is for a data got from a time clock machine which tracks emp in and out times and to report tot hours and late hours. I am doing it this way. return(trunc(:et_time-:st_time)+to_char(to_number(to_char(:et_time, 'HH24.MI'))-to_number(to_char(:st_time,'HH24.MI')),'9.99')); – Macky Sep 21 '11 at 07:01

score -2 · Answer 9 · answered Sep 18 '11 at 12:06

-2

you can work with the extract:

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

answered Sep 18 '11 at 12:06

roselan

3,755
1
20
20

2

i am getting the following error: SQL> SELECT start_time, end_time, extract(HOUR FROM end_time-start_time) || '.' || extract(MINUTE FROM end_time-start_time) From come_leav; SELECT start_time, end_time, extract(HOUR FROM end_time-start_time) || '.' || extract(MINUTE FROM end_time-start_time) From come_leav * ERROR at line 1: ORA-30076: invalid extract field for extract source – Macky Sep 19 '11 at 06:21
@roselan I get the same error as Macky. Would've been great if it worked, or could be made to work. – vapcguy Jul 29 '16 at 13:15

How to subtract 2 dates in oracle to get the result in hour and minute

9 Answers9

Example

SQL

Result

Linked