add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE OR REPLACE FUNCTION random_date(
p_from IN DATE,
p_to IN DATE
) RETURN DATE
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to - p_from + 1 );
END random_date;
/
CREATE OR REPLACE FUNCTION random_interval(
p_min_hours IN NUMBER,
p_max_hours IN NUMBER
) RETURN INTERVAL DAY TO SECOND
IS
BEGIN
RETURN floor(dbms_random.value(p_min_hours, p_max_hours)) * interval '1' hour
+ floor(dbms_random.value(0, 60)) * interval '1' minute
+ floor(dbms_random.value(0, 60)) * interval '1' second;
END random_interval;
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
start_date DATE,
end_date DATE
);
WITH cte (start_date, duration) AS (
SELECT
random_date(DATE '2022-04-01', DATE '2022-04-30'),
random_interval(1, 10)
FROM dual CONNECT BY level <= 50000
)
SELECT min(duration), max(duration)
FROM cte;
MIN(DURATION) MAX(DURATION)
+000000000 01:00:01.000000000 +000000000 09:59:59.000000000
WITH cte (start_date, duration) AS (
SELECT
random_date(DATE '2022-04-01', DATE '2022-04-30'),
random_interval(1, 10)
FROM dual CONNECT BY level <= 50
)
SELECT start_date, duration, start_date + duration as end_date
FROM cte
ORDER BY start_date;
START_DATE DURATION END_DATE
01-APR-2022 02:19:06 +000000000 06:57:38.000000000 01-APR-2022 09:16:44
01-APR-2022 12:39:33 +000000000 08:18:52.000000000 01-APR-2022 20:58:25
03-APR-2022 00:19:16 +000000000 03:26:38.000000000 03-APR-2022 03:45:54
03-APR-2022 11:35:55 +000000000 08:18:22.000000000 03-APR-2022 19:54:17
04-APR-2022 10:30:46 +000000000 05:35:58.000000000 04-APR-2022 16:06:44
05-APR-2022 08:01:28 +000000000 08:27:43.000000000 05-APR-2022 16:29:11
05-APR-2022 17:25:48 +000000000 02:47:11.000000000 05-APR-2022 20:12:59
05-APR-2022 20:23:30 +000000000 05:52:00.000000000 06-APR-2022 02:15:30
06-APR-2022 04:13:19 +000000000 03:19:10.000000000 06-APR-2022 07:32:29
06-APR-2022 21:47:13 +000000000 09:37:08.000000000 07-APR-2022 07:24:21
09-APR-2022 18:22:41 +000000000 06:43:44.000000000 10-APR-2022 01:06:25
10-APR-2022 03:24:01 +000000000 08:00:15.000000000 10-APR-2022 11:24:16
12-APR-2022 10:22:55 +000000000 09:30:17.000000000 12-APR-2022 19:53:12
12-APR-2022 22:45:25 +000000000 07:46:23.000000000 13-APR-2022 06:31:48
12-APR-2022 23:38:59 +000000000 01:48:21.000000000 13-APR-2022 01:27:20
13-APR-2022 04:24:57 +000000000 04:05:53.000000000 13-APR-2022 08:30:50
13-APR-2022 05:53:04 +000000000 03:35:55.000000000 13-APR-2022 09:28:59
13-APR-2022 18:38:24 +000000000 08:06:58.000000000 14-APR-2022 02:45:22
13-APR-2022 19:08:21 +000000000 02:07:47.000000000 13-APR-2022 21:16:08
15-APR-2022 13:06:47 +000000000 02:51:46.000000000 15-APR-2022 15:58:33
15-APR-2022 23:34:07 +000000000 08:48:24.000000000 16-APR-2022 08:22:31
16-APR-2022 00:58:32 +000000000 06:00:13.000000000 16-APR-2022 06:58:45
16-APR-2022 20:50:18 +000000000 02:15:07.000000000 16-APR-2022 23:05:25
18-APR-2022 05:59:52 +000000000 07:56:34.000000000 18-APR-2022 13:56:26
19-APR-2022 12:22:02 +000000000 05:35:54.000000000 19-APR-2022 17:57:56
19-APR-2022 18:48:28 +000000000 07:03:41.000000000 20-APR-2022 01:52:09
19-APR-2022 20:05:55 +000000000 05:40:42.000000000 20-APR-2022 01:46:37
19-APR-2022 22:25:20 +000000000 07:40:31.000000000 20-APR-2022 06:05:51
19-APR-2022 23:06:10 +000000000 02:13:35.000000000 20-APR-2022 01:19:45
21-APR-2022 06:19:40 +000000000 07:02:19.000000000 21-APR-2022 13:21:59
21-APR-2022 08:49:17 +000000000 04:04:33.000000000 21-APR-2022 12:53:50
22-APR-2022 11:35:43 +000000000 07:47:28.000000000 22-APR-2022 19:23:11
23-APR-2022 23:08:47 +000000000 03:48:35.000000000 24-APR-2022 02:57:22
24-APR-2022 01:00:06 +000000000 08:40:27.000000000 24-APR-2022 09:40:33
24-APR-2022 13:59:08 +000000000 03:30:04.000000000 24-APR-2022 17:29:12
24-APR-2022 21:09:01 +000000000 02:42:20.000000000 24-APR-2022 23:51:21
24-APR-2022 22:49:22 +000000000 08:26:17.000000000 25-APR-2022 07:15:39
25-APR-2022 21:00:54 +000000000 08:36:19.000000000 26-APR-2022 05:37:13
25-APR-2022 21:33:46 +000000000 04:33:43.000000000 26-APR-2022 02:07:29
26-APR-2022 07:50:12 +000000000 02:46:35.000000000 26-APR-2022 10:36:47
26-APR-2022 19:57:33 +000000000 01:42:13.000000000 26-APR-2022 21:39:46
27-APR-2022 02:02:30 +000000000 02:11:18.000000000 27-APR-2022 04:13:48
27-APR-2022 12:37:15 +000000000 05:34:26.000000000 27-APR-2022 18:11:41
28-APR-2022 04:02:29 +000000000 06:54:58.000000000 28-APR-2022 10:57:27
29-APR-2022 03:16:36 +000000000 03:26:27.000000000 29-APR-2022 06:43:03
29-APR-2022 05:41:12 +000000000 08:54:24.000000000 29-APR-2022 14:35:36
29-APR-2022 09:19:45 +000000000 06:02:23.000000000 29-APR-2022 15:22:08
29-APR-2022 13:47:46 +000000000 05:27:02.000000000 29-APR-2022 19:14:48
29-APR-2022 20:19:35 +000000000 02:53:01.000000000 29-APR-2022 23:12:36
30-APR-2022 02:20:34 +000000000 08:53:16.000000000 30-APR-2022 11:13:50
INSERT INTO t1 (start_date, end_date)
WITH cte (start_date, duration) AS (
SELECT
random_date(DATE '2022-04-01', DATE '2022-04-30'),
random_interval(1, 10)
FROM dual CONNECT BY level <= 50
)
SELECT start_date, start_date + duration
FROM cte;
50 rows affected
SELECT * FROM t1 ORDER BY start_date
SEQ_NUM START_DATE END_DATE
5 02-APR-2022 12:56:37 02-APR-2022 19:00:48
25 03-APR-2022 01:34:11 03-APR-2022 04:30:36
17 03-APR-2022 13:25:12 03-APR-2022 22:03:56
30 03-APR-2022 18:44:08 04-APR-2022 00:10:35
29 04-APR-2022 22:31:04 05-APR-2022 02:37:07
20 05-APR-2022 01:02:37 05-APR-2022 05:51:44
9 05-APR-2022 03:14:53 05-APR-2022 10:36:33
32 08-APR-2022 12:22:41 08-APR-2022 15:54:37
26 09-APR-2022 01:26:35 09-APR-2022 06:44:53
38 10-APR-2022 01:45:24 10-APR-2022 04:00:20
50 10-APR-2022 17:18:50 10-APR-2022 18:24:47
41 10-APR-2022 19:33:10 11-APR-2022 02:33:36
21 11-APR-2022 15:31:09 11-APR-2022 21:09:02
23 11-APR-2022 23:15:27 12-APR-2022 01:08:59
3 12-APR-2022 17:55:54 12-APR-2022 22:23:51
8 13-APR-2022 12:47:06 13-APR-2022 14:50:42
37 13-APR-2022 17:01:50 14-APR-2022 02:37:46
13 14-APR-2022 01:19:02 14-APR-2022 09:54:52
46 14-APR-2022 12:57:35 14-APR-2022 20:51:05
35 14-APR-2022 18:43:08 14-APR-2022 21:12:10
22 14-APR-2022 19:02:44 15-APR-2022 05:00:38
14 15-APR-2022 01:09:15 15-APR-2022 05:00:36
27 15-APR-2022 18:37:57 16-APR-2022 04:11:33
45 16-APR-2022 22:15:17 17-APR-2022 06:45:50
43 16-APR-2022 23:52:48 17-APR-2022 05:59:29
47 17-APR-2022 19:43:53 17-APR-2022 22:39:34
28 18-APR-2022 13:29:34 18-APR-2022 20:57:41
16 19-APR-2022 01:02:20 19-APR-2022 03:32:51
36 19-APR-2022 04:23:30 19-APR-2022 08:25:59
1 21-APR-2022 02:00:36 21-APR-2022 05:48:32
7 21-APR-2022 11:17:53 21-APR-2022 17:10:39
11 21-APR-2022 22:34:09 22-APR-2022 02:06:21
18 22-APR-2022 01:54:50 22-APR-2022 06:33:34
39 22-APR-2022 08:44:10 22-APR-2022 12:42:41
12 22-APR-2022 13:58:11 22-APR-2022 22:50:44
15 24-APR-2022 04:11:46 24-APR-2022 10:54:58
2 24-APR-2022 22:31:33 25-APR-2022 02:38:26
33 24-APR-2022 23:37:02 25-APR-2022 03:45:26
42 25-APR-2022 00:46:40 25-APR-2022 10:45:15
4 25-APR-2022 05:49:12 25-APR-2022 06:54:40
40 26-APR-2022 02:33:42 26-APR-2022 10:47:59
34 26-APR-2022 13:02:57 26-APR-2022 15:28:47
24 26-APR-2022 14:21:22 26-APR-2022 23:03:08
19 27-APR-2022 02:31:44 27-APR-2022 08:55:27
31 27-APR-2022 08:48:47 27-APR-2022 14:05:20
49 27-APR-2022 13:36:50 27-APR-2022 19:05:14
44 27-APR-2022 23:06:49 28-APR-2022 05:03:07
10 28-APR-2022 23:41:23 29-APR-2022 07:00:32
48 29-APR-2022 12:38:07 29-APR-2022 19:46:54
6 30-APR-2022 22:51:59 01-MAY-2022 02:19:15