By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
with ride(id, start_date, end_date) as
(select 1
, to_timestamp('2020-02-01 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
, to_timestamp('2020-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual
union all
select 2
, to_timestamp('2020-02-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
, to_timestamp('2020-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual
)
select id, start_date, end_date, (end_date - start_date) duration
from ride;
| ID | START_DATE | END_DATE | DURATION |
|---|---|---|---|
| 1 | 01-FEB-20 18.00.00.000000000 | 04-FEB-20 00.00.00.000000000 | +000000002 06:00:00.000000000 |
| 2 | 04-FEB-20 18.00.00.000000000 | 05-FEB-20 00.00.00.000000000 | +000000000 06:00:00.000000000 |
with ride(id, start_date, end_date) as
(select 1
, to_timestamp('2020-02-01 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
, to_timestamp('2020-02-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual
union all
select 2
, to_timestamp('2020-02-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
, to_timestamp('2020-02-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
from dual
)
select id, start_date, end_date, (end_date - start_date) duration
from ride
where (end_date - start_date) = (select max(end_date - start_date) from ride);
| ID | START_DATE | END_DATE | DURATION |
|---|---|---|---|
| 1 | 01-FEB-20 18.00.00.000000000 | 04-FEB-20 00.00.00.000000000 | +000000002 06:00:00.000000000 |