Oracle Dates and (Sometimes Random) Intervals

date_scratch.sql

WITH vals AS (
	SELECT 
		SESSIONTIMEZONE,
		CURRENT_DATE,
		FLOOR(DBMS_Random.Value(1,120)) r1,
		FLOOR(DBMS_Random.Value(1,120)) r2,
		(CURRENT_DATE - FLOOR(DBMS_Random.Value(1,120)) / (24 * 60)) HOURSRND,
		(CURRENT_DATE - INTERVAL '2' MINUTE) MINUTES2,
		(CURRENT_DATE - INTERVAL '20' HOUR) HOURS20,
		(CURRENT_DATE - INTERVAL '200' HOUR) HOURS200
	FROM DUAL
)
SELECT * FROM vals
UNION ALL SELECT * FROM vals
UNION ALL SELECT * FROM vals
UNION ALL SELECT * FROM vals
UNION ALL SELECT * FROM vals
UNION ALL SELECT * FROM vals
UNION ALL SELECT * FROM vals


-- Oracle's INTERVAL expression doesn't like the output from DBMS_RANDOM or even if you wrap it in a TO_CHAR (at least as of today).. 

output.md

The above sql results in..

SESSIONTIMEZONE  |CURRENT_DATE        |R1  |R2  |HOURSRND            |MINUTES2            |HOURS20             |HOURS200            |
-----------------|--------------------|----|----|--------------------|--------------------|--------------------|--------------------|
America/New_York |2017-11-01 11:23:33 |63  |37  |2017-11-01 10:18:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |77  |53  |2017-11-01 11:04:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |63  |100 |2017-11-01 11:05:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |62  |37  |2017-11-01 10:48:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |93  |73  |2017-11-01 10:46:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |119 |57  |2017-11-01 10:42:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |
America/New_York |2017-11-01 11:23:33 |47  |105 |2017-11-01 09:31:33 |2017-11-01 11:21:33 |2017-10-31 15:23:33 |2017-10-24 03:23:33 |