Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Calculate the time elapsed beetwen two states

Christian PitetJun 1 2020 — edited Jun 2 2020

Hi,

I want to caculate the time elapsed in the state "Disponible" from the following table :

CREATE TABLE "DT_DISPONIBLE"  

   ( "VEHICULE" NUMBER,  

"DISPONIBLE" VARCHAR2(36 BYTE),  

"DATE1" DATE

Insert into DT_DISPONIBLE (VEHICULE,DISPONIBLE,DATE1) values (15,'Disponible',to_date('12/05/2020 19:23:44','DD/MM/YYYY HH24:MI:SS')); 

Insert into DT_DISPONIBLE (VEHICULE,DISPONIBLE,DATE1) values (15,'Non disponible',to_date('12/05/2020 20:23:44','DD/MM/YYYY HH24:MI:SS')); 

Insert into DT_DISPONIBLE (VEHICULE,DISPONIBLE,DATE1) values (15,'Disponible',to_date('12/05/2020 21:23:44','DD/MM/YYYY HH24:MI:SS')); 

Insert into DT_DISPONIBLE (VEHICULE,DISPONIBLE,DATE1) values (15,'Non disponible',to_date('13/05/2020 18:59:01','DD/MM/YYYY HH24:MI:SS'));

Insert into DT_DISPONIBLE (VEHICULE,DISPONIBLE,DATE1) values (15,'Disponible',to_date('13/05/2020 19:59:01','DD/MM/YYYY HH24:MI:SS'));

With the above example, the time spent in the state 'Non disponible' would be 2 hours. I would like an output like this :

VEHICULE      NON DISPONIBLE

15                    2

How to achieve this ?

Regards,

Christian

Comments
Post Details
Added on Jun 1 2020
14 comments
244 views