CREATE TABLE emp_attendance (empid INTEGER,
start_date DATE,
end_date DATE );
/
I have a table called emp_attendance that I populate by editing the data manually, like so, which can become an arduous task if I want many rows.
INSERT INTO emp_attendance
(empid, start_date, end_date)
VALUES
(102, to_date('20200527 18:35:40','YYYYMMDD HH24:MI:SS'), to_date('20200528 03:13:42','YYYYMMDD HH24:MI:SS'));
I want to use dbms_random.value to create reasonable a reasonable end time.
By reasonable I mean the end time must be greater than the start time and the difference between the start and end time can be no more than 16 hours.
For example, valid differences can be
8hrs 12min 44 secs
11hrs 33 mins 08 secs
13hrs 25mins 59secs
…
…
…
I can use the following query to generate the start time
-- random date time
select to_date('2020-05-21', 'yyyy-mm-dd')+dbms_random.value(1,30) from dual
But I am unsure how to generate the end time (save the value of first time and add random values to meet my above criteria. In summary, I want to automate the above INSERT statement to create N number of emp_attendance using random date and times. Note the end_date can cross over midnight. Actually I am hoping to see some sample data like that.