I want to create random TIMESTAMP values in a range.

[Objective]
I want to create random TIMESTAMP values in a range

[Prerequisite]
environment:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

[Execution SQL]

TA TABLE 1: TIMESTAMP columns only

INSERT INTO TABLE 1
SELECT 
-- Obtain random dates from 20180101 to the current date and time
  SYSTEMESTAMP + MOD (ABS(DBMS_RANDOM.RANDOM()) , CURRENT_DATE-TO_DATE('20180101', 'YYYYMMDD') )
from
(SELECT 0 FROM ALL_CATALOG WHERE ROWNUM<=1000) 
, (SELECT 0 FROM ALL_CATALOG WHERE ROWNUM<=500)

[Results]
Up to seconds are random.Milliseconds are ".000000000" and do not become random

sql oracle

2022-09-30 14:09

1 Answers

*The user is SKATP=user37124.

@kunif's comment helped me achieve my goal.

@Kohei TAMURA, @kunif, thank you.

SELECT 
-- Obtain random dates from 20180101 to the current date and time
  SYSTEMESTAMP+(SYSTIMESTAMP-to_TIMESTAMP+MOD(ABS(DBMS_RANDOM.RANDOM())), CURRENT_DATE-TO_DATE('20180101', 'YYYYYMMDD'))))
FROM dual;

2022-09-30 14:09

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656