I want to create random TIMESTAMP values in a range.

Asked 2 months ago, Updated 2 months ago, 3 views

[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


© 2022 OneMinuteCode. All rights reserved.