Oracle Random Generation Time

Julian Date Definition

Julian Date: The valid date range is from January 1, 4713 BC to December 31, 9999. Julian Date is the number of days elapsed since 12 o'clock noon January 1, 4713.

Using it Julian date allows us to calculate how many days between two dates is more convenient.

Use To_char (date, ' J ') to convert a normal date to a Julian date

SELECT TO_CHAR(SYSDATE,‘J‘) FROM dual;

Convert days to dates using to_date (str, ' J ')

SELECT TO_DATE(‘2458117‘,‘J‘) FROM dual;SELECT TO_DATE(to_char(sysdate,‘J‘),‘J‘) FROM dual;

Calculate the number of days that the two dates are poor

Random generation Time

-- 生成2000-01-01到现在的随机日期，时分秒为00:00:00SELECT TO_DATE (TRUNC (DBMS_RANDOM.VALUE (TO_NUMBER (TO_CHAR (to_date(‘2000-01-01‘,‘yyyy-mm-dd‘),                                                              ‘J‘                                                             )                                                    ),                                          TO_NUMBER (TO_CHAR (SYSDATE, ‘J‘))                                         )                      ),                ‘J‘               )  FROM DUAL;  

A different approach

-- 生成2000-01-01到现在的随机日期,包括时分秒也是随机的SELECT to_date(‘2000-01-01‘, ‘yyyy-mm-dd‘) +       DBMS_RANDOM.VALUE(1, (to_char(sysdate, ‘J‘) - to_char(to_date(‘2000-01-01‘, ‘yyyy-mm-dd‘), ‘J‘)) * 24 * 60 * 60)       / 3600 / 24FROM dual;-- 或者SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(       to_number(to_char(to_date(‘2000-01-01‘,‘yyyy-mm-dd‘),‘J‘)),       to_number(to_char(sysdate,‘J‘)))),‘J‘)+       DBMS_RANDOM.VALUE(1,3600)/3600FROM dual;

Oracle Random Generation Time