Query to select rows based on time minus time-CodePudding

I want to build a query on oracle 11g R2 ,I have the following table :

let's take the first row as an example: first row F11 is 1 pm and T11 is 3 pm ,, the difference between 1 pm and 3 pm is 120 minute ,I have a variable X ,let's say X=10, what I want is to create a query to select rows between 1 pm and 3 pm divided by X , so I should have 12 row.

CodePudding user response：

I think it is a hierarchical query you're looking for. Those 12 rows would represent f11 (starting time) plus 120 minutes divided by the x value (which is 10) so - that's 12 minutes.

If that's so, here you go:

SQL> var x number;
SQL> exec :x := 10;

PL/SQL procedure successfully completed.

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

Session altered.

SQL> with test (id, f11, t11) as
  2    (select 1,
  3            to_date('28.03.2022 01:00', 'dd.mm.yyyy hh24:mi'),
  4            to_date('28.03.2022 03:00', 'dd.mm.yyyy hh24:mi')
  5     from dual union all
  6     --
  7     select 2,
  8            to_date('29.11.2021 01:00', 'dd.mm.yyyy hh24:mi'),
  9            to_date('29.11.2021 01:30', 'dd.mm.yyyy hh24:mi')
 10     from dual
 11    ),
 12  temp as
 13    (select id, f11, (t11 - f11) * (24 * 60) diff
 14     from test
 15    )
 16  select id,
 17    f11   (diff / :x) / (24 * 60) * (column_value - 1) val
 18  from temp
 19  cross join table(cast(multiset(select level from dual
 20                                 connect by level <= diff / :x
 21                                ) as sys.odcinumberlist))
 22  order by id, val;

        ID VAL
---------- -------------------
         1 28.03.2022 01:00:00
         1 28.03.2022 01:12:00
         1 28.03.2022 01:24:00
         1 28.03.2022 01:36:00
         1 28.03.2022 01:48:00
         1 28.03.2022 02:00:00
         1 28.03.2022 02:12:00
         1 28.03.2022 02:24:00
         1 28.03.2022 02:36:00
         1 28.03.2022 02:48:00
         1 28.03.2022 03:00:00
         1 28.03.2022 03:12:00
         2 29.11.2021 01:00:00
         2 29.11.2021 01:03:00
         2 29.11.2021 01:06:00

15 rows selected.

SQL>