-1

How can I show interval between 2 timestamps in 'hh24:mi' format?

I have a Table with two Datestamps StartTime and EndTime between which I would like to see the difference in Hours and Minutes as HH24:mi

The difference is never above 24 hours but StartTime can be on day 1 and EndTime can be on day .

Example :

StartTime = 19/02/2019 22:52:42 
EndTime = 20/02/2019 02:56:42 

Result wanted = 04:04

So far the best results I managed to have are : 

4,8 : ROUND ((EndTime - StartTime) * 24,2) INTERVAL 

4:4 :  EXTRACT (hour from numtodsinterval (EndTime - add_months (StartTime, floor (months_between (EndTime,StartTime))), 'day')) || ':'
 || EXTRACT (minute from numtodsinterval (EndTime - add_months (StartTime, floor (months_between (EndTime, StartTime))), 'day'))

Hours & Minutes in a seperate field : Hrs : 4 | Min : 4

trunc(((86400*(EndTime-StartTime))/60)/60)-24*(trunc((((86400*(EndTime-StartTime))/60)/60)/24)) "Hrs"

trunc((86400*(EndTime-StartTime))/60)-60*(trunc(((86400*(EndTime-StartTime))/60)/60)) "Min"
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
Dumbo
  • 27
  • 1
  • 1
  • 7
  • What is the actual column data type - you mentioned timestamps, but also 'datestamps', and you haven't shown any fractional seconds or time zones, so they could just be dates? Your attempts also aren't very clear, can you show the results you get from each one? If they work at all I think they must actually be dates not timestamps... – Alex Poole Feb 20 '19 at 10:35
  • 1
    [Possible duplicate](https://stackoverflow.com/q/45598811/266304) if they are timestamp columns. [Possible duplicate](https://stackoverflow.com/q/50835774/266304) if they are date columns. – Alex Poole Feb 20 '19 at 10:43
  • if the difference is more than a day what is the output you want? – Hijesh V L Feb 20 '19 at 10:50
  • @HijeshVl - "The difference is never above 24 hours" (whether that is actually always going to really be true is another matter, of course *8-) – Alex Poole Feb 20 '19 at 10:51
  • @Alex Poole : StartTime & EndTime are both = DATE Field so this format : 20/02/2019 5:38:01 – Dumbo Feb 20 '19 at 12:10
  • @AlexPoole : Results : ROUND ((EndTime - StartTime) * 24,2) has 4,8 as result. EXTRACT (hour from numtodsinterval (EndTime - add_months (StartTime, floor (months_between (EndTime,StartTime))), 'day')) || ':' || EXTRACT (minute from numtodsinterval (EndTime - add_months (StartTime, floor (months_between (EndTime, StartTime))), 'day')) has 4:4 as result – Dumbo Feb 20 '19 at 12:11
  • Please [edit your question](https://stackoverflow.com/posts/54784102/edit) to add more information, particularly things that need to be formatted to be readable. It seems like the second duplicate I linked to covers what you're trying to do though? – Alex Poole Feb 20 '19 at 12:15

2 Answers2

0

I wrote an anonymous block using your values. but if you are querying from a table it is not required. you can apply this logic in select query itself to get output.

declare
start_time date;
end_time date;
output number;
f_out varchar2(10);

begin
start_time :=to_Date('19/02/2019 22:52:42','dd/mm/yyyy hh24:mi:ss');
end_time:= to_Date('20/02/2019 02:56:42','dd/mm/yyyy hh24:mi:ss');
select (end_time-start_time) into output from dual;
--output := trunc(output*86400/3600);
f_out:=lpad(trunc(output*86400/3600),2,'0')||':'||lpad(mod(output*86400,3600)/60,2,'0');
dbms_output.put_line(f_out);

end;
Hijesh V L
  • 191
  • 1
  • 5
0

You can do this by processing dates:

select to_char(date '2000-01-01' + (end_time - start_time), 'hh24:mi')
from (select to_Date('2019-02-19 22:52:42', 'yyyy-mm-dd hh24:mi:ss') as start_time,
             to_Date('2019-02-19 02:56:42', 'yyyy-mm-dd hh24:mi:ss') as end_time
      from dual
     ) t

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786