I'm looking to generate times to create a schedule and I'm using the following query to get a random times, which works fine.
select TRUNC(sysdate) + DBMS_RANDOM.value(0,86400)/86400 from dual;
First, how can I modify this SELECT statement to ONLY generate a random time from between 00:00:00 and 18:59:00
Second, how can I assure that the seconds will ALWAYS be '00' as I only want to store MMDDYYYY HH24:MI is there a CONSTRAINT I can add to a table to ensure the seconds are '00'
Last, how can I modify my query to choose a random HH24:MI then have subsequent rows be incremented by 6 - 10 minutes intervals.
Below is my query and desired output. Thanks to all who answer.
CREATE TABLE. schedule_hdr AS
SELECT level AS
schedule_id,
'Schedule ' || level AS schedule_name
FROM dual
CONNECT BY level <= 2;
with rws as ( select level rn from dual connect by level <= 5 ),
scheds as ( select sh.*, round (dbms_random.value(1,5) ) n from schedule_hdr sh )
select. schedule_id,
TRUNC(sysdate) + DBMS_RANDOM.value(0,86400)/86400
from rws
join scheds s on rn <= n
Order by schedule_id;
1 07112020 01:43:00 -- random
1 07112020 01:49:00 -- INCREMENT
2 07112020 04:51:00 -- random
2 07112020 04:59:00 -- INCREMENT
2 07112020 05:05:00 -- INCREMENT
Aucun commentaire:
Enregistrer un commentaire