1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Random time generation

Discussion in 'SQL PL/SQL' started by Shanmugapriya, Oct 4, 2016.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Hi,
    I need help on random time generation.

    I basically have two columns. In_time (DATE) and Out_time (DATE). I need to generate random date with time for both these columns for the same day.

    for ex: In_time : 04-oct-2016 09:14:15
    Out_time: 04-oct-2016 12:11:13
     
  2. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    São Paulo - Brasil
    Hi,
    try this way, you need to build a pl sql block to feed a table or create a function

    select to_char((TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/86400)),'dd-mon-yyyy hh24:mi:ss') in_time,
    to_char((TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/1440)),'dd-mon-yyyy hh24:mi:ss') out_time
    from dual;
     
  3. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Bangalore
    Thank you for your reply. I executed this query and it works. But this is restricting to only today's date. Is there is a way to generate for different dates as well.
    However both in_time and out_time should be for same day.
     
  4. Roberto Spernega

    Roberto Spernega Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    São Paulo - Brasil
    Yes, you simply replace the SYSDATE for other dates

    declare
    --
    v_date date;
    v_counter number := 0;
    v_in_time date;
    v_out_time date;
    --
    begin
    while v_Counter <= 10 loop
    v_date := trunc(sysdate) - v_counter;
    v_in_time := (TRUNC(v_date) + (TRUNC(DBMS_RANDOM.value(0,1000))/86400));
    v_out_time := (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/1440));
    --
    dbms_output.put_line('in time '||to_char(v_in_time,'dd-mon-yyyy hh24:mi:ss'));
    dbms_output.put_line('out time '||to_char(v_out_time,'dd-mon-yyyy hh24:mi:ss'));
    v_Counter := nvl(v_Counter,0) + 1;
    end loop;
    end;
    /
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Correcting a typo:

    Code (SQL):
    SELECT
        trunc(sysdate) + level start_time,
        trunc(sysdate) + level + dbms_random.VALUE(0,3599)/3600 end_time
    FROM dual
    CONNECT BY level <= 24;
     
    It's always a good idea to test the code you're posting to ensure it is error-free and works 'as advertised'.