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 TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
start_date DATE,
end_date DATE
);
DECLARE
l_cnt NUMBER := 0;
l_it NUMBER := 0;
l_ts NUMBER := dbms_utility.get_time();
l_start_date DATE;
l_end_date DATE;
BEGIN
WHILE l_cnt < 50
LOOP
l_it := l_it + 1;
SELECT random_date (DATE '2022-04-01', DATE '2022-04-30') start_date,
random_date (DATE '2022-04-01', DATE '2022-04-30') end_date
INTO l_start_date, l_end_date
FROM DUAL;

IF l_end_date - l_start_date BETWEEN 1 / 24 AND 10 / 24
THEN
INSERT INTO t1 (start_date, end_date)
VALUES (l_start_date, l_end_date);

l_cnt := l_cnt + SQL%ROWCOUNT;
END IF;
END LOOP;
dbms_output.put_line((dbms_utility.get_time() - l_ts)/100 || 's: ' || l_it);
END;
/
1 rows affected

dbms_output:
.68s: 4093
DECLARE
l_cnt NUMBER := 0;
l_it NUMBER := 0;
l_ts NUMBER := dbms_utility.get_time();
l_start_date DATE;
l_end_date DATE;
BEGIN
WHILE l_cnt < 50
LOOP
l_it := l_it + 1;
l_start_date := random_date (DATE '2022-04-01', DATE '2022-04-30');
l_end_date := random_date (DATE '2022-04-01', DATE '2022-04-30');

IF l_end_date - l_start_date BETWEEN 1 / 24 AND 10 / 24
THEN
INSERT INTO t1 (start_date, end_date)
VALUES (l_start_date, l_end_date);

l_cnt := l_cnt + SQL%ROWCOUNT;
END IF;
END LOOP;
dbms_output.put_line((dbms_utility.get_time() - l_ts)/100 || 's: ' || l_it);
END;
/
1 rows affected

dbms_output:
.04s: 5191
DECLARE
l_cnt NUMBER := 0;
l_it NUMBER := 0;
l_ts NUMBER := dbms_utility.get_time();
l_start_date DATE;
l_end_date DATE;
BEGIN
WHILE l_cnt < 50
LOOP
l_it := l_it + 1;
l_start_date := random_date (DATE '2022-04-01', DATE '2022-04-30');
l_end_date := random_date (l_start_date, DATE '2022-04-30');

IF l_end_date - l_start_date BETWEEN 1 / 24 AND 10 / 24
THEN
INSERT INTO t1 (start_date, end_date)
VALUES (l_start_date, l_end_date);

l_cnt := l_cnt + SQL%ROWCOUNT;
END IF;
END LOOP;
dbms_output.put_line((dbms_utility.get_time() - l_ts)/100 || 's: ' || l_it);
END;
/
1 rows affected

dbms_output:
.02s: 1037
SELECT t1.*, (end_date - start_date) * interval '1' day FROM t1 ORDER BY end_date - start_date desc
SEQ_NUM START_DATE END_DATE (END_DATE-START_DATE)*INTERVAL'1'DAY
28 03-APR-2022 13:22:16 03-APR-2022 23:21:17 +000000000 09:59:01.000000000
55 13-APR-2022 10:11:28 13-APR-2022 20:07:22 +000000000 09:55:54.000000000
98 07-APR-2022 08:03:12 07-APR-2022 17:53:45 +000000000 09:50:33.000000000
83 27-APR-2022 13:47:24 27-APR-2022 23:35:46 +000000000 09:48:22.000000000
82 26-APR-2022 03:47:33 26-APR-2022 13:35:02 +000000000 09:47:29.000000000
36 15-APR-2022 22:25:54 16-APR-2022 08:08:18 +000000000 09:42:24.000000000
97 27-APR-2022 05:08:51 27-APR-2022 14:40:10 +000000000 09:31:19.000000000
24 01-APR-2022 12:38:00 01-APR-2022 21:57:41 +000000000 09:19:41.000000000
5 05-APR-2022 20:38:57 06-APR-2022 05:57:21 +000000000 09:18:24.000000000
116 01-APR-2022 12:08:37 01-APR-2022 21:14:46 +000000000 09:06:09.000000000
101 30-APR-2022 04:51:37 30-APR-2022 13:57:12 +000000000 09:05:35.000000000
26 12-APR-2022 03:15:58 12-APR-2022 12:21:05 +000000000 09:05:07.000000000
51 09-APR-2022 05:56:21 09-APR-2022 15:00:08 +000000000 09:03:47.000000000
40 26-APR-2022 01:22:38 26-APR-2022 10:14:34 +000000000 08:51:56.000000000
2 18-APR-2022 23:37:30 19-APR-2022 08:28:15 +000000000 08:50:45.000000000
4 13-APR-2022 22:39:43 14-APR-2022 07:27:49 +000000000 08:48:06.000000000
50 27-APR-2022 16:37:44 28-APR-2022 01:22:38 +000000000 08:44:54.000000000
122 27-APR-2022 18:05:23 28-APR-2022 02:38:31 +000000000 08:33:08.000000000
49 13-APR-2022 00:44:01 13-APR-2022 09:16:42 +000000000 08:32:41.000000000
34 04-APR-2022 16:32:26 05-APR-2022 01:02:31 +000000000 08:30:05.000000000
135 30-APR-2022 02:47:25 30-APR-2022 11:17:16 +000000000 08:29:51.000000000
78 29-APR-2022 09:38:39 29-APR-2022 18:05:47 +000000000 08:27:08.000000000
22 11-APR-2022 13:43:35 11-APR-2022 22:09:46 +000000000 08:26:11.000000000
85 14-APR-2022 07:17:24 14-APR-2022 15:31:56 +000000000 08:14:32.000000000
25 24-APR-2022 19:18:23 25-APR-2022 03:30:56 +000000000 08:12:33.000000000
56 10-APR-2022 04:34:27 10-APR-2022 12:41:16 +000000000 08:06:49.000000000
71 02-APR-2022 10:02:33 02-APR-2022 18:06:32 +000000000 08:03:59.000000000
92 11-APR-2022 06:03:00 11-APR-2022 14:05:38 +000000000 08:02:38.000000000
8 19-APR-2022 00:55:25 19-APR-2022 08:48:11 +000000000 07:52:46.000000000
125 26-APR-2022 19:07:55 27-APR-2022 02:54:37 +000000000 07:46:42.000000000
88 22-APR-2022 18:39:47 23-APR-2022 02:23:22 +000000000 07:43:35.000000000
61 03-APR-2022 04:03:30 03-APR-2022 11:46:11 +000000000 07:42:41.000000000
147 29-APR-2022 14:31:01 29-APR-2022 22:07:32 +000000000 07:36:31.000000000
41 20-APR-2022 04:47:48 20-APR-2022 12:21:11 +000000000 07:33:23.000000000
103 07-APR-2022 01:28:28 07-APR-2022 08:58:04 +000000000 07:29:36.000000000
54 05-APR-2022 23:48:33 06-APR-2022 07:17:01 +000000000 07:28:28.000000000
106 30-APR-2022 15:49:28 30-APR-2022 23:16:19 +000000000 07:26:51.000000000
31 16-APR-2022 09:56:30 16-APR-2022 17:16:37 +000000000 07:20:07.000000000
120 22-APR-2022 17:25:55 23-APR-2022 00:42:37 +000000000 07:16:42.000000000
68 24-APR-2022 12:05:35 24-APR-2022 19:17:15 +000000000 07:11:40.000000000
21 26-APR-2022 11:28:45 26-APR-2022 18:38:56 +000000000 07:10:11.000000000
149 23-APR-2022 01:10:26 23-APR-2022 08:19:50 +000000000 07:09:24.000000000
138 21-APR-2022 19:44:12 22-APR-2022 02:45:57 +000000000 07:01:45.000000000
126 30-APR-2022 08:11:25 30-APR-2022 15:12:53 +000000000 07:01:28.000000000
66 14-APR-2022 10:26:00 14-APR-2022 17:22:22 +000000000 06:56:22.000000000
37 23-APR-2022 02:08:05 23-APR-2022 08:58:35 +000000000 06:50:30.000000000
14 02-APR-2022 20:39:49 03-APR-2022 03:29:52 +000000000 06:50:03.000000000
107 27-APR-2022 03:59:41 27-APR-2022 10:49:10 +000000000 06:49:29.000000000
79 27-APR-2022 04:27:57 27-APR-2022 11:16:43 +000000000 06:48:46.000000000
45 01-APR-2022 05:24:27 01-APR-2022 12:11:45 +000000000 06:47:18.000000000
1 03-APR-2022 02:44:14 03-APR-2022 09:26:49 +000000000 06:42:35.000000000
121 26-APR-2022 04:15:19 26-APR-2022 10:56:47 +000000000 06:41:28.000000000
91 03-APR-2022 09:33:34 03-APR-2022 16:13:57 +000000000 06:40:23.000000000
39 19-APR-2022 12:38:51 19-APR-2022 19:16:07 +000000000 06:37:16.000000000
87 18-APR-2022 20:05:31 19-APR-2022 02:41:46 +000000000 06:36:15.000000000
148 16-APR-2022 20:22:18 17-APR-2022 02:51:37 +000000000 06:29:19.000000000
118 25-APR-2022 13:52:17 25-APR-2022 20:20:05 +000000000 06:27:48.000000000
64 26-APR-2022 20:17:24 27-APR-2022 02:43:03 +000000000 06:25:39.000000000
136 30-APR-2022 02:16:15 30-APR-2022 08:38:49 +000000000 06:22:34.000000000
60 05-APR-2022 04:00:04 05-APR-2022 10:21:57 +000000000 06:21:53.000000000
16 08-APR-2022 17:57:34 09-APR-2022 00:18:58 +000000000 06:21:24.000000000
9 12-APR-2022 04:14:22 12-APR-2022 10:34:58 +000000000 06:20:36.000000000
74 13-APR-2022 08:07:28 13-APR-2022 14:26:35 +000000000 06:19:07.000000000
110 30-APR-2022 11:54:35 30-APR-2022 18:12:52 +000000000 06:18:17.000000000
42 10-APR-2022 21:20:38 11-APR-2022 03:38:39 +000000000 06:18:01.000000000
12 30-APR-2022 06:28:38 30-APR-2022 12:43:23 +000000000 06:14:45.000000000
6 24-APR-2022 03:16:40 24-APR-2022 09:31:17 +000000000 06:14:37.000000000
69 13-APR-2022 08:55:44 13-APR-2022 15:07:53 +000000000 06:12:09.000000000
29 28-APR-2022 15:39:33 28-APR-2022 21:49:23 +000000000 06:09:50.000000000
141 30-APR-2022 14:25:10 30-APR-2022 20:34:40 +000000000 06:09:30.000000000
3 28-APR-2022 05:48:10 28-APR-2022 11:55:37 +000000000 06:07:27.000000000
80 18-APR-2022 10:40:42 18-APR-2022 16:42:08 +000000000 06:01:26.000000000
38 07-APR-2022 03:29:13 07-APR-2022 09:27:40 +000000000 05:58:27.000000000
27 06-APR-2022 00:34:18 06-APR-2022 06:31:41 +000000000 05:57:23.000000000
127 30-APR-2022 11:59:01 30-APR-2022 17:56:10 +000000000 05:57:09.000000000
94 11-APR-2022 09:17:59 11-APR-2022 15:11:23 +000000000 05:53:24.000000000
77 27-APR-2022 19:09:43 28-APR-2022 01:02:47 +000000000 05:53:04.000000000
47 15-APR-2022 23:45:36 16-APR-2022 05:30:22 +000000000 05:44:46.000000000
15 14-APR-2022 08:44:56 14-APR-2022 14:27:07 +000000000 05:42:11.000000000
130 19-APR-2022 00:03:07 19-APR-2022 05:44:33 +000000000 05:41:26.000000000
43 02-APR-2022 20:30:15 03-APR-2022 02:11:13 +000000000 05:40:58.000000000
89 04-APR-2022 09:50:30 04-APR-2022 15:28:44 +000000000 05:38:14.000000000
90 04-APR-2022 14:43:59 04-APR-2022 20:09:12 +000000000 05:25:13.000000000
10 19-APR-2022 14:57:41 19-APR-2022 20:22:40 +000000000 05:24:59.000000000
70 15-APR-2022 14:40:45 15-APR-2022 20:04:55 +000000000 05:24:10.000000000
35 28-APR-2022 11:30:57 28-APR-2022 16:48:12 +000000000 05:17:15.000000000
73 10-APR-2022 04:55:16 10-APR-2022 10:11:33 +000000000 05:16:17.000000000
58 22-APR-2022 13:41:54 22-APR-2022 18:56:19 +000000000 05:14:25.000000000
30 03-APR-2022 10:40:41 03-APR-2022 15:51:36 +000000000 05:10:55.000000000
59 11-APR-2022 01:07:46 11-APR-2022 06:08:14 +000000000 05:00:28.000000000
137 30-APR-2022 07:55:19 30-APR-2022 12:55:45 +000000000 05:00:26.000000000
145 01-APR-2022 06:55:23 01-APR-2022 11:50:23 +000000000 04:55:00.000000000
76 20-APR-2022 17:14:14 20-APR-2022 22:06:29 +000000000 04:52:15.000000000
62 01-APR-2022 19:44:52 02-APR-2022 00:31:34 +000000000 04:46:42.000000000
142 26-APR-2022 13:16:02 26-APR-2022 18:01:34 +000000000 04:45:32.000000000
48 16-APR-2022 20:12:00 17-APR-2022 00:52:37 +000000000 04:40:37.000000000
86 30-APR-2022 11:25:04 30-APR-2022 16:02:21 +000000000 04:37:17.000000000
17 11-APR-2022 02:43:45 11-APR-2022 07:20:59 +000000000 04:37:14.000000000
115 30-APR-2022 12:39:58 30-APR-2022 17:06:44 +000000000 04:26:46.000000000
96 05-APR-2022 07:17:03 05-APR-2022 11:34:37 +000000000 04:17:34.000000000
67 11-APR-2022 08:33:43 11-APR-2022 12:46:28 +000000000 04:12:45.000000000
102 23-APR-2022 22:28:27 24-APR-2022 02:36:03 +000000000 04:07:36.000000000
112 30-APR-2022 07:11:34 30-APR-2022 11:14:51 +000000000 04:03:17.000000000
119 30-APR-2022 16:07:27 30-APR-2022 20:05:16 +000000000 03:57:49.000000000
144 26-APR-2022 10:05:10 26-APR-2022 13:54:58 +000000000 03:49:48.000000000
65 19-APR-2022 16:21:12 19-APR-2022 20:10:45 +000000000 03:49:33.000000000
19 01-APR-2022 20:29:25 02-APR-2022 00:15:57 +000000000 03:46:32.000000000
7 01-APR-2022 16:41:52 01-APR-2022 20:26:14 +000000000 03:44:22.000000000
95 18-APR-2022 09:30:26 18-APR-2022 13:10:27 +000000000 03:40:01.000000000
53 06-APR-2022 09:04:26 06-APR-2022 12:43:44 +000000000 03:39:18.000000000
84 14-APR-2022 00:54:55 14-APR-2022 04:33:22 +000000000 03:38:27.000000000
109 21-APR-2022 12:25:07 21-APR-2022 16:02:30 +000000000 03:37:23.000000000
11 13-APR-2022 15:10:17 13-APR-2022 18:36:53 +000000000 03:26:36.000000000
93 26-APR-2022 18:31:59 26-APR-2022 21:53:01 +000000000 03:21:02.000000000
20 22-APR-2022 08:38:48 22-APR-2022 11:57:29 +000000000 03:18:41.000000000
52 28-APR-2022 17:36:13 28-APR-2022 20:52:26 +000000000 03:16:13.000000000
131 30-APR-2022 12:04:21 30-APR-2022 15:20:04 +000000000 03:15:43.000000000
105 30-APR-2022 19:20:22 30-APR-2022 22:35:57 +000000000 03:15:35.000000000
18 29-APR-2022 19:44:30 29-APR-2022 22:58:50 +000000000 03:14:20.000000000
23 17-APR-2022 15:02:17 17-APR-2022 18:13:36 +000000000 03:11:19.000000000
143 30-APR-2022 16:04:18 30-APR-2022 19:13:58 +000000000 03:09:40.000000000
32 13-APR-2022 12:11:53 13-APR-2022 15:17:36 +000000000 03:05:43.000000000
72 22-APR-2022 03:00:23 22-APR-2022 06:04:54 +000000000 03:04:31.000000000
63 25-APR-2022 05:07:42 25-APR-2022 07:57:20 +000000000 02:49:38.000000000
113 25-APR-2022 18:27:39 25-APR-2022 21:14:14 +000000000 02:46:35.000000000
13 14-APR-2022 05:34:09 14-APR-2022 08:20:36 +000000000 02:46:27.000000000
75 02-APR-2022 06:55:36 02-APR-2022 09:40:39 +000000000 02:45:03.000000000
99 12-APR-2022 03:59:35 12-APR-2022 06:43:31 +000000000 02:43:56.000000000
124 24-APR-2022 08:10:04 24-APR-2022 10:50:27 +000000000 02:40:23.000000000
111 26-APR-2022 14:09:39 26-APR-2022 16:45:55 +000000000 02:36:16.000000000
104 28-APR-2022 12:10:06 28-APR-2022 14:43:51 +000000000 02:33:45.000000000
132 24-APR-2022 15:45:51 24-APR-2022 18:15:12 +000000000 02:29:21.000000000
114 30-APR-2022 00:13:24 30-APR-2022 02:41:33 +000000000 02:28:09.000000000
108 23-APR-2022 18:50:04 23-APR-2022 21:11:34 +000000000 02:21:30.000000000
133 29-APR-2022 17:56:15 29-APR-2022 20:17:29 +000000000 02:21:14.000000000
46 20-APR-2022 04:59:22 20-APR-2022 07:18:06 +000000000 02:18:44.000000000
44 22-APR-2022 16:11:18 22-APR-2022 18:13:45 +000000000 02:02:27.000000000
134 20-APR-2022 17:10:13 20-APR-2022 19:06:21 +000000000 01:56:08.000000000
123 28-APR-2022 22:56:37 29-APR-2022 00:43:59 +000000000 01:47:22.000000000
139 30-APR-2022 01:29:04 30-APR-2022 03:12:37 +000000000 01:43:33.000000000
146 30-APR-2022 20:17:50 30-APR-2022 21:49:53 +000000000 01:32:03.000000000
140 02-APR-2022 21:55:11 02-APR-2022 23:26:32 +000000000 01:31:21.000000000
150 30-APR-2022 17:10:57 30-APR-2022 18:34:45 +000000000 01:23:48.000000000
33 12-APR-2022 03:44:57 12-APR-2022 05:04:49 +000000000 01:19:52.000000000
117 23-APR-2022 19:06:37 23-APR-2022 20:25:30 +000000000 01:18:53.000000000
128 19-APR-2022 08:09:38 19-APR-2022 09:22:49 +000000000 01:13:11.000000000
81 18-APR-2022 10:35:52 18-APR-2022 11:48:05 +000000000 01:12:13.000000000
100 23-APR-2022 07:39:11 23-APR-2022 08:51:10 +000000000 01:11:59.000000000
129 06-APR-2022 08:28:54 06-APR-2022 09:34:34 +000000000 01:05:40.000000000
57 30-APR-2022 07:53:18 30-APR-2022 08:57:20 +000000000 01:04:02.000000000