generate list of minutes between two dates (SQL)

Question

Hi stackoverflow community,

I would like to achieve the following.

I currently have tbl1. But i would like to achieve a constant stream of data between dt_sta_cet and dt_sto_cet(see desired table) and also a constant data stream between dt_sto_cet from one row and dt_sta_cet from the next row.

Tbl1

id  dt_sta_cet        dt_sto_cet         s_t
1   2019-12-13 09:00  2019-12-13 09:05    DT
1   2019-12-13 09:10  2019-12-13 09:14    DT
1   2019-12-13 09:17  2019-12-13 09:20    DT

Tbl1(desired)

id  dt                   s_t    u_t
1   2019-12-13 09:00      DT    null
1   2019-12-13 09:01      DT    null
1   2019-12-13 09:02      DT    null
1   2019-12-13 09:03      DT    null
1   2019-12-13 09:04      DT    null
1   2019-12-13 09:05      DT    null
1   2019-12-13 09:06      null   Y
1   2019-12-13 09:07      null   Y
1   2019-12-13 09:08      null   Y
1   2019-12-13 09:09      null   Y
1   2019-12-13 09:10      DT     Y
1   2019-12-13 09:11      DT     Y

EDIT:

To get the Dt_sta_cet/ dt_sto_cet time i have the following query.

    With tbl1 as ( select d.a_s_t, mach.a_id,

 to_char(to_date(f.f_sto_sta_dt_id,'YYYYMMDD') + (f.f_sto_sta_time_id/86400),
    'YYYY-MM-DD HH24:MI:SS') as sto_sta_dt
    ,to_char(to_date(f.f_sto_sto_dt_id,'YYYYMMDD') + (f.f_sto_sto_time_id/86400),
    'YYYY-MM-DD HH24:MI:SS') as sto_sto_dt

    from f_sto f
    left join d_sto d on f.f_sto_id= d.k_id
    left join d_mach mach on f.f_mach_id=mach.k_id
    where d.a_s_t = 'DT')

    select tbl1.a_id, tbl1.sto_sta_dt, tbl1.sto_sto_dt, tbl1.a_s_t,
    to_char(to_date(st_sta_dt, 'YYYY-MM-DD HH24:MI:SS')-1/24, 'YYYY-MM-DD HH24:MI:SS' as dt_sta_cet
    , to_char(to_date(st_sto_dt, 'YYYY-MM-DD HH24:MI:SS')-1/24, 'YYYY-MM-DD HH24:MI:SS' as dt_sto_cet 

Answer 1

A version that use analytic function and a recursive sub-query and does not require a self-join (which may read the table/index twice):

Oracle Setup:

CREATE TABLE Tbl1 ( id, dt_sta_cet, dt_sto_cet, s_t ) AS
SELECT 1, DATE '2019-12-13' + INTERVAL '09:00' HOUR TO MINUTE, DATE '2019-12-13' + INTERVAL '09:05' HOUR TO MINUTE, 'DT' FROM DUAL UNION ALL
SELECT 1, DATE '2019-12-13' + INTERVAL '09:10' HOUR TO MINUTE, DATE '2019-12-13' + INTERVAL '09:14' HOUR TO MINUTE, 'DT' FROM DUAL UNION ALL
SELECT 1, DATE '2019-12-13' + INTERVAL '09:17' HOUR TO MINUTE, DATE '2019-12-13' + INTERVAL '09:20' HOUR TO MINUTE, 'DT' FROM DUAL UNION ALL
SELECT 2, DATE '2019-12-13' + INTERVAL '09:00' HOUR TO MINUTE, DATE '2019-12-13' + INTERVAL '09:05' HOUR TO MINUTE, 'DT' FROM DUAL;

Query:

WITH times ( id, start_dt, stop_dt, next_start_dt, s_t, u_t ) AS (
  SELECT id,
         dt_sta_cet,
         dt_sto_cet,
         LEAD( dt_sta_cet - INTERVAL '1' MINUTE, 1, dt_sto_cet )
           OVER ( PARTITION BY id ORDER BY dt_sta_cet ),
         s_t,
         CASE
         WHEN COUNT(*) OVER ( PARTITION BY id ORDER BY dt_sta_cet ) > 1
         THEN 'Y'
         END
  FROM   Tbl1
UNION ALL
  SELECT id,
         start_dt + INTERVAL '1' MINUTE,
         stop_dt,
         next_start_dt,
         CASE
         WHEN start_dt + INTERVAL '1' MINUTE <= stop_dt
         THEN s_t
         ELSE NULL
         END,
         CASE
         WHEN start_dt + INTERVAL '1' MINUTE <= stop_dt
         THEN u_t
         ELSE 'Y'
         END
  FROM   times
  WHERE  start_dt + INTERVAL '1' MINUTE <= next_start_dt
)
SELECT id,
       start_dt AS dt,
       s_t,
       u_t
FROM   times
ORDER BY id, dt

Output:

ID | DT                  | S_T  | U_T 
-: | :------------------ | :--- | :---
 1 | 2019-12-13 09:00:00 | DT   | null
 1 | 2019-12-13 09:01:00 | DT   | null
 1 | 2019-12-13 09:02:00 | DT   | null
 1 | 2019-12-13 09:03:00 | DT   | null
 1 | 2019-12-13 09:04:00 | DT   | null
 1 | 2019-12-13 09:05:00 | DT   | null
 1 | 2019-12-13 09:06:00 | null | Y   
 1 | 2019-12-13 09:07:00 | null | Y   
 1 | 2019-12-13 09:08:00 | null | Y   
 1 | 2019-12-13 09:09:00 | null | Y   
 1 | 2019-12-13 09:10:00 | DT   | Y   
 1 | 2019-12-13 09:11:00 | DT   | Y   
 1 | 2019-12-13 09:12:00 | DT   | Y   
 1 | 2019-12-13 09:13:00 | DT   | Y   
 1 | 2019-12-13 09:14:00 | DT   | Y   
 1 | 2019-12-13 09:15:00 | null | Y   
 1 | 2019-12-13 09:16:00 | null | Y   
 1 | 2019-12-13 09:17:00 | DT   | Y   
 1 | 2019-12-13 09:18:00 | DT   | Y   
 1 | 2019-12-13 09:19:00 | DT   | Y   
 1 | 2019-12-13 09:20:00 | DT   | Y   
 2 | 2019-12-13 09:00:00 | DT   | null
 2 | 2019-12-13 09:01:00 | DT   | null
 2 | 2019-12-13 09:02:00 | DT   | null
 2 | 2019-12-13 09:03:00 | DT   | null
 2 | 2019-12-13 09:04:00 | DT   | null
 2 | 2019-12-13 09:05:00 | DT   | null

db<>fiddle here

Answer 2

I am not sure about the u_t in the expected result.

But to generate all the minutes, you can use something like this(Updated after MT0's comment):

SQL> WITH DATAA (ID, DT_STA_CET, DT_STO_CET,S_T) AS
  2  (
  3  SELECT 1, TO_DATE('2019-12-13 09:00','YYYY-MM-DD HH24:MI'),TO_DATE('2019-12-13 09:05','YYYY-MM-DD HH24:MI'),'DT' FROM DUAL UNION ALL
  4  SELECT 1, TO_DATE('2019-12-13 09:10','YYYY-MM-DD HH24:MI'),TO_DATE('2019-12-13 09:14','YYYY-MM-DD HH24:MI'),'DT' FROM DUAL UNION ALL
  5  SELECT 1, TO_DATE('2019-12-13 09:17','YYYY-MM-DD HH24:MI'),TO_DATE('2019-12-13 09:20','YYYY-MM-DD HH24:MI'),'DT' FROM DUAL
  6  ),
  7  CTE(ID, DT) AS (
  8      SELECT ID, START_DT + ( column_value - 1 ) / 1440 AS DT
  9      FROM
 10          (
 11              SELECT
 12                  ID,
 13                  MIN(DT_STA_CET) AS START_DT,
 14                  MAX(DT_STO_CET) AS END_DT
 15              FROM DATAA
 16              GROUP BY ID
 17          )
 18          cross join 
 19       table(cast(multiset(select level from dual
 20                           connect by level <= ( END_DT - START_DT ) * 1440 + 1
 21                          ) as sys.odcinumberlist))
 22  )
 23  SELECT
 24          C.ID, C.DT, D.S_T
 25  FROM
 26      CTE C
 27      LEFT JOIN DATAA D ON ( C.DT BETWEEN D.DT_STA_CET AND D.DT_STO_CET
 28                             AND C.ID = D.ID )
 29  ORDER BY C.DT;

Result:

        ID DT                S_
---------- ----------------- --
         1 13-dec-2019 09:00 DT
         1 13-dec-2019 09:01 DT
         1 13-dec-2019 09:02 DT
         1 13-dec-2019 09:03 DT
         1 13-dec-2019 09:04 DT
         1 13-dec-2019 09:05 DT
         1 13-dec-2019 09:06
         1 13-dec-2019 09:07
         1 13-dec-2019 09:08
         1 13-dec-2019 09:09
         1 13-dec-2019 09:10 DT
         1 13-dec-2019 09:11 DT
         1 13-dec-2019 09:12 DT
         1 13-dec-2019 09:13 DT
         1 13-dec-2019 09:14 DT
         1 13-dec-2019 09:15
         1 13-dec-2019 09:16
         1 13-dec-2019 09:17 DT
         1 13-dec-2019 09:18 DT
         1 13-dec-2019 09:19 DT
         1 13-dec-2019 09:20 DT

21 rows selected.

SQL>

Cheers!!