As part of our QA and unit testing procedures some of us are asked to help out and create process to generate or manipulate data.
I created 2 functions, which are supposed to generate random dates and timestamps between a range of dates
I almost have this working except for a caveat that I can't seem to figure out and can use a bit of help.
If you run the INSERT query below I can seem to generate a date or timestamp for the last day of the month, which in this case is April 30, 2022 regardless of how many rows I generate. This leads me to believe I have a bug in my code, which I can use some help figuring out. I can widen the date range to May 1 2020 but I would like to understand where I went wrong.
I apologize for the two separate queries on the same data but I have yet to figure out how to combine both queries into a single query yet.
Below is my test CASE. Thanks in advance to all who respond and for your expertise.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
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);
END random_date;
/
CREATE OR REPLACE FUNCTION random_timestamp(
p_from IN TIMESTAMP,
p_to IN TIMESTAMP
) RETURN TIMESTAMP
IS
BEGIN
RETURN p_from + DBMS_RANDOM.VALUE() * (p_to -p_from);
END random_timestamp;
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE,
ts TIMESTAMP
);
INSERT INTO t1 (dt, ts )
SELECT
random_date(DATE '2022-04-01', DATE '2022-04-30'),
random_timestamp(DATE '2022-04-01', DATE '2022-04-30')
FROM
dual CONNECT BY level <= 10000;
/*
Missing data for April 30
*/
select trunc(dt), count(*)
from t1
group by trunc(dt)
Order by trunc(dt);
select trunc(ts), count(*)
from t1
group by trunc(ts)
Order by trunc(ts);