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

Question

I want to subtract 2 dates and present the result per hour and minute in decimal.

I have the following table, and I do it this way, but the result is not as desired.

There are a few minor variations, I'm sure this is a simple arithmetic, but I do not understand.

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

 START_TIME END_TIME (END_TIME-START_TIME) * 24    
 ------------------- ------------------- ------------ ------------      
 06/21/2011 2:00:00 p.m. 11/21/2011 4:55 p.m. 2.9166667      
 06/21/2011 07:00:00 AM 06/21/2011 16:50:00 9.8333333      
 06/21/2011 07:20:00 p.m. 21-06-2011 16:30:00 9.1666667      

I want to get the result (end_time-start_time) as shown 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 this?

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 

It should be noted that for those who go through this code, the decimal parts are VALID minutes, not hours. .5 therefore represents 50 minutes , not 30 minutes .

Answer 2

try it

 round(to_number(end_time - start_time) * 24) 

Answer 3

Oracle presents dates as the number of days, so (end_time-start_time)*24 gives you hours. Suppose you have this number (e.g. 2.9166667 ) in column h . Then you can easily convert it to the desired format: 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 

Answer 4

try the following:

  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 5

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 the result of the string, 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 this is my personal preference.

for more than 24 hours, I would prefix

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

Yes, since the oracle counts days, accurate to the second, you are dealing with arithmetic problems. Once, because you only process minutes (so you can round your number to trunc(days*24*60+0.5)/24/60 24/60), but the binary arithmetic inaccuracy on 1/24/60 can still cause problems .

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 can be quite confusing for the average value, since decimal 7.50 would suggest seven and a half hours or at least 7 hours 50 minutes, against a time equal to 7 hours 10 minutes.

Answer 6

This query is very useful to me, and if any authority wants a difference between start_date and end_date over time, like HH: MI: SS, 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; 

Answer 7

This is a very ugly way to do this, and this first part does not exactly ask the OP question, but it does make it possible to get the results by subtracting 2 date fields - in my case, CREATED_DATE and CREATED_DATE is presented 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 displays the result as x years, x months, x days, x hours, x minutes. It could be reformatted as you like by changing the concatenated strings.

To more accurately answer the question, I went ahead and wrote how to get the total number of hours in 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; 

Answer 8

you can work with the statement:

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