I have a table named DAILY_EVENTS and it holds data like this:

EVENT_ID   | EVENT_TYPE       | From_date           | To_date
----------- ------------ ----- --------------------- ---------------------
3          | ACCIDENT         | 2013-01-05 01:14:47 | 2013-01-05 01:14:55
6          | TERRORISM        | 2013-01-05 22:55:20 | 2013-01-05 22:56:00
8          | NATURAL DISASTER | 2013-01-05 16:05:30 | 2013-01-05 16:05:39

I need to find empty time spots to insert more rows for this day with these conditions:

a) Time ranges cannot be superimposed. i.e. new rows From_date and To_date cannot be in the range between 01:14:47 to 01:14:55, or 22:55:20 to 22:56:00 and so on.

b) 10 seconds between From_date time and To_date time i.e 01:14:00 - 01:14:10

c) minimum of 2 seconds between the To_date of the event previously inserted and the from_date of the current event i.e. if First row is 01:14:00 - 01:14:10, then second row must starts minimum at 01:14:12, and the To_Date will be 01:14:22

A summary of 2 new rows (last ones) with allowed data could be these:

EVENT_ID   | EVENT_TYPE       | From_date           | To_date
----------- ------------ ----- --------------------- ---------------------
3          | ACCIDENT         | 2013-01-05 01:14:47 | 2013-01-05 01:14:55
6          | TERRORISM        | 2013-01-05 22:55:20 | 2013-01-05 22:56:00
8          | NATURAL DISASTER | 2013-01-05 16:05:30 | 2013-01-05 16:05:39
3          | ACCIDENT         | 2013-01-05 00:00:00 | 2013-01-05 00:00:10
6          | ACCIDENT         | 2013-01-05 00:00:12 | 2013-01-05 00:00:22

I'm kinda blocked with this requirement, I don't know how can I start with, any idea?

CodePudding user response：

You can generate all the gaps using:

SELECT LAG(to_date, 1, TRUNC(from_date)) OVER (ORDER BY From_date) AS gap_start,
       from_date AS gap_end
FROM   daily_events
WHERE  to_date   > DATE '2013-01-05'
AND    from_date < DATE '2013-01-05'   INTERVAL '1' DAY
UNION ALL
SELECT COALESCE(MAX(to_date), DATE '2013-01-05'),
       DATE '2013-01-05'   INTERVAL '1' DAY
FROM   daily_events
WHERE  to_date   > DATE '2013-01-05'
AND    from_date < DATE '2013-01-05'   INTERVAL '1' DAY;

Which, for the sample data:

CREATE TABLE daily_events (EVENT_ID, EVENT_TYPE, From_date, To_date) AS
SELECT 3, 'ACCIDENT',         DATE '2013-01-05'   INTERVAL '01:14:47' HOUR TO SECOND, DATE '2013-01-05'   INTERVAL '01:14:55' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 6, 'TERRORISM',        DATE '2013-01-05'   INTERVAL '22:55:20' HOUR TO SECOND, DATE '2013-01-05'   INTERVAL '22:56:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 8, 'NATURAL DISASTER', DATE '2013-01-05'   INTERVAL '16:05:30' HOUR TO SECOND, DATE '2013-01-05'   INTERVAL '16:05:39' HOUR TO SECOND FROM DUAL;

Outputs:

GAP_START	GAP_END
2013-01-05 00:00:00	2013-01-05 01:14:47
2013-01-05 01:14:55	2013-01-05 16:05:30
2013-01-05 16:05:39	2013-01-05 22:55:20
2013-01-05 22:56:00	2013-01-06 00:00:00

Then you just need to pick a 10-second range within those gaps.

You can generate all the possible non-overlapping 10-second intervals, starting from 2-seconds after the end of the previous interval, using a recursive query:

WITH gaps (gap_start, gap_end) AS (
  SELECT LAG(to_date, 1, TRUNC(from_date)) OVER (ORDER BY From_date),
         from_date
  FROM   daily_events
  WHERE  to_date   > DATE '2013-01-05'
  AND    from_date < DATE '2013-01-05'   INTERVAL '1' DAY
UNION ALL
  SELECT COALESCE(MAX(to_date), DATE '2013-01-05'),
         DATE '2013-01-05'   INTERVAL '1' DAY
  FROM   daily_events
  WHERE  to_date   > DATE '2013-01-05'
  AND    from_date < DATE '2013-01-05'   INTERVAL '1' DAY
),
intervals (gap_start, gap_end, int_start, int_end) AS (
  SELECT gap_start,
         gap_end,
         gap_start   INTERVAL '2' SECOND,
         gap_start   INTERVAL '12' SECOND
  FROM   gaps
  WHERE  gap_start   INTERVAL '14' SECOND <= gap_end
UNION ALL
  SELECT gap_start,
         gap_end,
         int_end   INTERVAL '2' SECOND,
         int_end   INTERVAL '12' SECOND
  FROM   intervals
  WHERE  int_end   INTERVAL '14' SECOND <= gap_end
)
SEARCH DEPTH FIRST BY gap_start SET int_order
SELECT int_start, int_end
FROM   intervals;

Which, for the sample data, outputs:

INT_START	INT_END
2013-01-05 00:00:02	2013-01-05 00:00:12
2013-01-05 00:00:14	2013-01-05 00:00:24
2013-01-05 00:00:26	2013-01-05 00:00:36
...	...
2013-01-05 01:14:02	2013-01-05 01:14:12
2013-01-05 01:14:14	2013-01-05 01:14:24
2013-01-05 01:14:26	2013-01-05 01:14:36
2013-01-05 01:14:57	2013-01-05 01:15:07
2013-01-05 01:15:09	2013-01-05 01:15:19
2013-01-05 01:15:21	2013-01-05 01:15:31
...	...
2013-01-05 16:04:45	2013-01-05 16:04:55
2013-01-05 16:04:57	2013-01-05 16:05:07
2013-01-05 16:05:09	2013-01-05 16:05:19
2013-01-05 16:05:41	2013-01-05 16:05:51
2013-01-05 16:05:53	2013-01-05 16:06:03
2013-01-05 16:06:05	2013-01-05 16:06:15
...	...
2013-01-05 22:56:26	2013-01-05 22:56:36
2013-01-05 22:56:38	2013-01-05 22:56:48
2013-01-05 22:56:50	2013-01-05 22:57:00
2013-01-05 22:57:02	2013-01-05 22:57:12
2013-01-05 22:57:14	2013-01-05 22:57:24
2013-01-05 22:57:26	2013-01-05 22:57:36
...	...
2013-01-05 23:59:14	2013-01-05 23:59:24
2013-01-05 23:59:26	2013-01-05 23:59:36
2013-01-05 23:59:38	2013-01-05 23:59:48

db<>fiddle here