samedi 11 juillet 2020

Oracle random time and interval

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