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 TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
start_date DATE,
end_date DATE
);
INSERT /*+ WITH_PLSQL */ INTO t1 (start_date, end_date)
WITH
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;
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;
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
create function diff_as_string (p_from date, p_to date)
return varchar2 is
l_interval interval day(3) to second(0);
begin
l_interval := cast(p_to as timestamp) - cast(p_from as timestamp);
return extract (day from l_interval) || 'Days'
|| extract (hour from l_interval) || 'Hours'
|| extract (minute from l_interval) || 'Minutes'
|| extract (second from l_interval) || 'Seconds';
end diff_as_string;
/
-- 1 second less than 1000 days
insert into t1 values (9999, date '2019-11-21' + interval '1' second, date '2022-08-17');
1 rows affected
select seq_num, start_date, end_date,
diff_as_string(start_date, end_date) as diff
from t1
order by seq_num;
| SEQ_NUM | START_DATE | END_DATE | DIFF |
|---|---|---|---|
| 1 | 20-APR-2022 03:46:04 | 20-APR-2022 07:44:30 | 0Days3Hours58Minutes26Seconds |
| 2 | 12-APR-2022 01:37:07 | 12-APR-2022 10:54:40 | 0Days9Hours17Minutes33Seconds |
| 3 | 12-APR-2022 16:20:44 | 12-APR-2022 20:36:13 | 0Days4Hours15Minutes29Seconds |
| 4 | 03-APR-2022 01:24:53 | 03-APR-2022 07:57:52 | 0Days6Hours32Minutes59Seconds |
| 5 | 01-APR-2022 21:33:20 | 01-APR-2022 23:50:13 | 0Days2Hours16Minutes53Seconds |
| 6 | 21-APR-2022 17:37:27 | 21-APR-2022 19:48:16 | 0Days2Hours10Minutes49Seconds |
| 7 | 13-APR-2022 12:42:07 | 13-APR-2022 21:28:09 | 0Days8Hours46Minutes2Seconds |
| 8 | 03-APR-2022 01:55:22 | 03-APR-2022 06:58:00 | 0Days5Hours2Minutes38Seconds |
| 9 | 03-APR-2022 19:34:18 | 04-APR-2022 02:10:45 | 0Days6Hours36Minutes27Seconds |
| 10 | 14-APR-2022 07:16:35 | 14-APR-2022 14:47:57 | 0Days7Hours31Minutes22Seconds |
| 11 | 27-APR-2022 10:47:56 | 27-APR-2022 17:36:52 | 0Days6Hours48Minutes56Seconds |
| 12 | 05-APR-2022 00:33:08 | 05-APR-2022 01:37:48 | 0Days1Hours4Minutes40Seconds |
| 13 | 07-APR-2022 01:41:40 | 07-APR-2022 04:46:52 | 0Days3Hours5Minutes12Seconds |
| 14 | 02-APR-2022 14:21:18 | 02-APR-2022 16:28:21 | 0Days2Hours7Minutes3Seconds |
| 15 | 03-APR-2022 19:21:38 | 04-APR-2022 02:51:04 | 0Days7Hours29Minutes26Seconds |
| 16 | 14-APR-2022 03:12:30 | 14-APR-2022 07:24:58 | 0Days4Hours12Minutes28Seconds |
| 17 | 30-APR-2022 05:29:02 | 30-APR-2022 14:26:32 | 0Days8Hours57Minutes30Seconds |
| 18 | 11-APR-2022 03:46:24 | 11-APR-2022 05:08:18 | 0Days1Hours21Minutes54Seconds |
| 19 | 26-APR-2022 12:30:58 | 26-APR-2022 14:20:47 | 0Days1Hours49Minutes49Seconds |
| 20 | 08-APR-2022 07:18:40 | 08-APR-2022 10:49:17 | 0Days3Hours30Minutes37Seconds |
| 21 | 02-APR-2022 19:27:14 | 03-APR-2022 03:42:21 | 0Days8Hours15Minutes7Seconds |
| 22 | 20-APR-2022 11:53:43 | 20-APR-2022 14:35:54 | 0Days2Hours42Minutes11Seconds |
| 23 | 10-APR-2022 04:36:44 | 10-APR-2022 06:31:36 | 0Days1Hours54Minutes52Seconds |
| 24 | 01-APR-2022 18:22:50 | 02-APR-2022 02:14:41 | 0Days7Hours51Minutes51Seconds |
| 25 | 16-APR-2022 04:09:40 | 16-APR-2022 11:36:08 | 0Days7Hours26Minutes28Seconds |
| 26 | 04-APR-2022 23:10:17 | 05-APR-2022 03:45:20 | 0Days4Hours35Minutes3Seconds |
| 27 | 11-APR-2022 16:50:40 | 12-APR-2022 01:52:57 | 0Days9Hours2Minutes17Seconds |
| 28 | 14-APR-2022 02:09:52 | 14-APR-2022 08:50:01 | 0Days6Hours40Minutes9Seconds |
| 29 | 19-APR-2022 01:24:44 | 19-APR-2022 08:57:49 | 0Days7Hours33Minutes5Seconds |
| 30 | 16-APR-2022 10:53:02 | 16-APR-2022 20:33:18 | 0Days9Hours40Minutes16Seconds |
| 31 | 29-APR-2022 11:57:15 | 29-APR-2022 20:20:36 | 0Days8Hours23Minutes21Seconds |
| 32 | 25-APR-2022 01:35:31 | 25-APR-2022 04:21:52 | 0Days2Hours46Minutes21Seconds |
| 33 | 26-APR-2022 21:09:29 | 27-APR-2022 00:26:51 | 0Days3Hours17Minutes22Seconds |
| 34 | 21-APR-2022 11:12:55 | 21-APR-2022 12:53:20 | 0Days1Hours40Minutes25Seconds |
| 35 | 23-APR-2022 20:33:47 | 23-APR-2022 23:58:52 | 0Days3Hours25Minutes5Seconds |
| 36 | 25-APR-2022 15:00:12 | 25-APR-2022 19:21:06 | 0Days4Hours20Minutes54Seconds |
| 37 | 26-APR-2022 03:06:41 | 26-APR-2022 09:05:13 | 0Days5Hours58Minutes32Seconds |
| 38 | 20-APR-2022 12:41:12 | 20-APR-2022 17:49:39 | 0Days5Hours8Minutes27Seconds |
| 39 | 20-APR-2022 02:34:28 | 20-APR-2022 11:25:24 | 0Days8Hours50Minutes56Seconds |
| 40 | 20-APR-2022 14:06:54 | 20-APR-2022 18:31:07 | 0Days4Hours24Minutes13Seconds |
| 41 | 06-APR-2022 05:25:24 | 06-APR-2022 09:05:31 | 0Days3Hours40Minutes7Seconds |
| 42 | 23-APR-2022 07:06:11 | 23-APR-2022 11:40:32 | 0Days4Hours34Minutes21Seconds |
| 43 | 01-APR-2022 21:55:13 | 02-APR-2022 07:05:33 | 0Days9Hours10Minutes20Seconds |
| 44 | 09-APR-2022 23:09:54 | 10-APR-2022 00:21:41 | 0Days1Hours11Minutes47Seconds |
| 45 | 10-APR-2022 21:47:43 | 11-APR-2022 02:42:10 | 0Days4Hours54Minutes27Seconds |
| 46 | 23-APR-2022 08:18:35 | 23-APR-2022 18:07:04 | 0Days9Hours48Minutes29Seconds |
| 47 | 04-APR-2022 06:26:06 | 04-APR-2022 08:44:26 | 0Days2Hours18Minutes20Seconds |
| 48 | 14-APR-2022 17:31:54 | 14-APR-2022 19:10:50 | 0Days1Hours38Minutes56Seconds |
| 49 | 19-APR-2022 13:45:43 | 19-APR-2022 14:58:16 | 0Days1Hours12Minutes33Seconds |
| 50 | 22-APR-2022 22:46:46 | 23-APR-2022 08:03:38 | 0Days9Hours16Minutes52Seconds |
| 9999 | 21-NOV-2019 00:00:01 | 17-AUG-2022 00:00:00 | 999Days23Hours59Minutes59Seconds |
select seq_num, start_date, end_date,
extract (day from diff_interval) || 'Days'
|| extract (hour from diff_interval) || 'Hours'
|| extract (minute from diff_interval) || 'Minutes'
|| extract (second from diff_interval) || 'Seconds' as diff
from (
select seq_num, start_date, end_date,
cast(end_date as timestamp) - cast(start_date as timestamp) as diff_interval
from t1
)
order by seq_num;
| SEQ_NUM | START_DATE | END_DATE | DIFF |
|---|---|---|---|
| 1 | 20-APR-2022 03:46:04 | 20-APR-2022 07:44:30 | 0Days3Hours58Minutes26Seconds |
| 2 | 12-APR-2022 01:37:07 | 12-APR-2022 10:54:40 | 0Days9Hours17Minutes33Seconds |
| 3 | 12-APR-2022 16:20:44 | 12-APR-2022 20:36:13 | 0Days4Hours15Minutes29Seconds |
| 4 | 03-APR-2022 01:24:53 | 03-APR-2022 07:57:52 | 0Days6Hours32Minutes59Seconds |
| 5 | 01-APR-2022 21:33:20 | 01-APR-2022 23:50:13 | 0Days2Hours16Minutes53Seconds |
| 6 | 21-APR-2022 17:37:27 | 21-APR-2022 19:48:16 | 0Days2Hours10Minutes49Seconds |
| 7 | 13-APR-2022 12:42:07 | 13-APR-2022 21:28:09 | 0Days8Hours46Minutes2Seconds |
| 8 | 03-APR-2022 01:55:22 | 03-APR-2022 06:58:00 | 0Days5Hours2Minutes38Seconds |
| 9 | 03-APR-2022 19:34:18 | 04-APR-2022 02:10:45 | 0Days6Hours36Minutes27Seconds |
| 10 | 14-APR-2022 07:16:35 | 14-APR-2022 14:47:57 | 0Days7Hours31Minutes22Seconds |
| 11 | 27-APR-2022 10:47:56 | 27-APR-2022 17:36:52 | 0Days6Hours48Minutes56Seconds |
| 12 | 05-APR-2022 00:33:08 | 05-APR-2022 01:37:48 | 0Days1Hours4Minutes40Seconds |
| 13 | 07-APR-2022 01:41:40 | 07-APR-2022 04:46:52 | 0Days3Hours5Minutes12Seconds |
| 14 | 02-APR-2022 14:21:18 | 02-APR-2022 16:28:21 | 0Days2Hours7Minutes3Seconds |
| 15 | 03-APR-2022 19:21:38 | 04-APR-2022 02:51:04 | 0Days7Hours29Minutes26Seconds |
| 16 | 14-APR-2022 03:12:30 | 14-APR-2022 07:24:58 | 0Days4Hours12Minutes28Seconds |
| 17 | 30-APR-2022 05:29:02 | 30-APR-2022 14:26:32 | 0Days8Hours57Minutes30Seconds |
| 18 | 11-APR-2022 03:46:24 | 11-APR-2022 05:08:18 | 0Days1Hours21Minutes54Seconds |
| 19 | 26-APR-2022 12:30:58 | 26-APR-2022 14:20:47 | 0Days1Hours49Minutes49Seconds |
| 20 | 08-APR-2022 07:18:40 | 08-APR-2022 10:49:17 | 0Days3Hours30Minutes37Seconds |
| 21 | 02-APR-2022 19:27:14 | 03-APR-2022 03:42:21 | 0Days8Hours15Minutes7Seconds |
| 22 | 20-APR-2022 11:53:43 | 20-APR-2022 14:35:54 | 0Days2Hours42Minutes11Seconds |
| 23 | 10-APR-2022 04:36:44 | 10-APR-2022 06:31:36 | 0Days1Hours54Minutes52Seconds |
| 24 | 01-APR-2022 18:22:50 | 02-APR-2022 02:14:41 | 0Days7Hours51Minutes51Seconds |
| 25 | 16-APR-2022 04:09:40 | 16-APR-2022 11:36:08 | 0Days7Hours26Minutes28Seconds |
| 26 | 04-APR-2022 23:10:17 | 05-APR-2022 03:45:20 | 0Days4Hours35Minutes3Seconds |
| 27 | 11-APR-2022 16:50:40 | 12-APR-2022 01:52:57 | 0Days9Hours2Minutes17Seconds |
| 28 | 14-APR-2022 02:09:52 | 14-APR-2022 08:50:01 | 0Days6Hours40Minutes9Seconds |
| 29 | 19-APR-2022 01:24:44 | 19-APR-2022 08:57:49 | 0Days7Hours33Minutes5Seconds |
| 30 | 16-APR-2022 10:53:02 | 16-APR-2022 20:33:18 | 0Days9Hours40Minutes16Seconds |
| 31 | 29-APR-2022 11:57:15 | 29-APR-2022 20:20:36 | 0Days8Hours23Minutes21Seconds |
| 32 | 25-APR-2022 01:35:31 | 25-APR-2022 04:21:52 | 0Days2Hours46Minutes21Seconds |
| 33 | 26-APR-2022 21:09:29 | 27-APR-2022 00:26:51 | 0Days3Hours17Minutes22Seconds |
| 34 | 21-APR-2022 11:12:55 | 21-APR-2022 12:53:20 | 0Days1Hours40Minutes25Seconds |
| 35 | 23-APR-2022 20:33:47 | 23-APR-2022 23:58:52 | 0Days3Hours25Minutes5Seconds |
| 36 | 25-APR-2022 15:00:12 | 25-APR-2022 19:21:06 | 0Days4Hours20Minutes54Seconds |
| 37 | 26-APR-2022 03:06:41 | 26-APR-2022 09:05:13 | 0Days5Hours58Minutes32Seconds |
| 38 | 20-APR-2022 12:41:12 | 20-APR-2022 17:49:39 | 0Days5Hours8Minutes27Seconds |
| 39 | 20-APR-2022 02:34:28 | 20-APR-2022 11:25:24 | 0Days8Hours50Minutes56Seconds |
| 40 | 20-APR-2022 14:06:54 | 20-APR-2022 18:31:07 | 0Days4Hours24Minutes13Seconds |
| 41 | 06-APR-2022 05:25:24 | 06-APR-2022 09:05:31 | 0Days3Hours40Minutes7Seconds |
| 42 | 23-APR-2022 07:06:11 | 23-APR-2022 11:40:32 | 0Days4Hours34Minutes21Seconds |
| 43 | 01-APR-2022 21:55:13 | 02-APR-2022 07:05:33 | 0Days9Hours10Minutes20Seconds |
| 44 | 09-APR-2022 23:09:54 | 10-APR-2022 00:21:41 | 0Days1Hours11Minutes47Seconds |
| 45 | 10-APR-2022 21:47:43 | 11-APR-2022 02:42:10 | 0Days4Hours54Minutes27Seconds |
| 46 | 23-APR-2022 08:18:35 | 23-APR-2022 18:07:04 | 0Days9Hours48Minutes29Seconds |
| 47 | 04-APR-2022 06:26:06 | 04-APR-2022 08:44:26 | 0Days2Hours18Minutes20Seconds |
| 48 | 14-APR-2022 17:31:54 | 14-APR-2022 19:10:50 | 0Days1Hours38Minutes56Seconds |
| 49 | 19-APR-2022 13:45:43 | 19-APR-2022 14:58:16 | 0Days1Hours12Minutes33Seconds |
| 50 | 22-APR-2022 22:46:46 | 23-APR-2022 08:03:38 | 0Days9Hours16Minutes52Seconds |
| 9999 | 21-NOV-2019 00:00:01 | 17-AUG-2022 00:00:00 | 999Days23Hours59Minutes59Seconds |