CODEWITHSUNDEEP
oracle-databaserandomplsqltimestamp
Lumpi
Lumpi

Reputation: 89

PLSQL: random timestamp between 2 timestamps

I have a timestamp which represents the current timestamp:

now := CURRENT_TIMESTAMP;
tomorrow := now + INTERVAL '1' DAY;

Now I want to render a random timestamp between those 2 values, I tried with DBMS_RANDOM but it doesn't work

Any Help?

Upvotes: 1

Views: 3951

Answers (1)

mmmmmpie
mmmmmpie

Reputation: 3039

This will generate a random timestamp between the current timestamp and sysdate+1: 

SELECT TO_DATE(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') + 
       dbms_random.value(0, TO_DATE(SYSDATE, 'MM/DD/YYYY HH24:MI:SS') - 
       TO_DATE(SYSDATE, 'MM/DD/YYYY HH24:MI:SS')+1) 
FROM dual;

As PL/SQL:

DECLARE
  l_ran_time TIMESTAMP;
BEGIN
  SELECT SYSDATE + 
         dbms_random.value(0, SYSDATE - 
         SYSDATE+1) 
  INTO l_ran_time
  FROM dual;
  dbms_output.put_line(l_ran_time);
END;
/

Upvotes: 5

Related Questions