CODEWITHSUNDEEP
sqldatabaseoracle-database
anton4o
anton4o

Reputation: 499

change the output format of the time elapsed between two hours

I have the following SQL Query:

    SELECT 
    TO_CHAR(Event1, 'HH24:MI:SS'),
    TO_CHAR(Event2, 'HH24:MI:SS'),
    TO_CHAR((Event1-Event2) * -1440) AS Elapsed
...

This gives me the time elapsed between the two hours at which event1 and event2 happened in minutes.

My question: how do I enforce the time elapsed to be displayed not in minutes but in the following format HH24:MI:SS?

Upvotes: 2

Views: 1695

Answers (2)

Victor Grimaldo
Victor Grimaldo

Reputation: 58

Try

select 
TRUNC(event1-event2)||':'||
TRUNC(MOD(event1-event2),1)*24)||':'||
TRUNC(MOD(MOD(event1-event2),1)*24,1)*60)||':'||
TRUNC(MOD(MOD(MOD((event1-event2),1)*24,1)*60,1)*60) as elapsed

Upvotes: 1

DazzaL
DazzaL

Reputation: 21993

you could convert to TIMESTAMP which result in an interval datatype

SQL> create table test(a date, b date);

Table created.

SQL> insert into test values (sysdate - 1.029384, sysdate);

1 row created.

SQL> select 1440*(b-a) diff_in_secs from test;

DIFF_IN_SECS
------------
  1482.31667

SQL> select (cast(b as timestamp)-cast(a as timestamp)) diff_in_secs from test;

DIFF_IN_SECS
---------------------------------------------------------------------------
+000000001 00:42:19.000000

you can extract individual elements with extract('hour' from your_interval_expression) etc.

SQL> select extract(day from diff)||'d '||extract(hour from diff)||'h '||extract(minute from diff)||'m '||extract(second from diff)||'s' from (select (cast(b as timestamp)-cast
(a as timestamp)) diff from test);

EXTRACT(DAYFROMDIFF)||'D'||EX
--------------------------------------------------------------------------------
1d 0h 42m 19s

Upvotes: 1

Related Questions