How to calculate duration of timestamp when only given start_time and the end_time is the next row in start_time using PL SQL?

I have this data where I need to calculate the duration of end_time - start_time, but I am only given the start_time and the end_time would be the next row in start_time. How can I do the select statement to get the end_time using PL SQL?

Current Table Example:

Expected Table:

Solution

One option is to use lead analytic function which returns the next date value (sorted by start_time); then subtract end - start to get duration which is measured in number of days (as that's what you get when you subtract two date datatype values). If you want, you can format it so that it looks prettier.

Setting date format (so that we'd know what is what):

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

Sample data:

SQL> with test (product, start_time) as
  2    (select 'A', to_date('01.07.2023 12:25:30', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  3     select 'B', to_date('01.07.2023 12:29:35', 'dd.mm.yyyy hh24:mi:ss') from dual union all
  4     select 'C', to_date('01.07.2023 12:32:02', 'dd.mm.yyyy hh24:mi:ss') from dual
  5    )

Query begins here:

  6  select
  7    product,
  8    start_time,
  9    nvl(lead(start_time) over (order by start_time), start_time) as end_time,
 10    --
 11    nvl(lead(start_time) over (order by start_time), start_time) - start_time as duration_in_days
 12  from test
 13  order by start_Time;

PRODUCT  START_TIME          END_TIME            DURATION_IN_DAYS
-------- ------------------- ------------------- ----------------
A        01.07.2023 12:25:30 01.07.2023 12:29:35       ,002835648
B        01.07.2023 12:29:35 01.07.2023 12:32:02       ,001701389
C        01.07.2023 12:32:02 01.07.2023 12:32:02                0

SQL>