The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Generating a Random Date
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
This blog post was authored by Jim Knicely.
I can easily generate a random integer value using the Vertica built-in RANDOMINT function.
For example:
dbadmin=> SELECT randomint(10) "Random 0-9", dbadmin-> randomint(10) "Random 0-9", dbadmin-> randomint(10) "Random 0-9"; Random 0-9 | Random 0-9 | Random 0-9 ------------+------------+------------ 6 | 4 | 0 (1 row)
But what if I need a random date? Luckily in Vertica I can create my own SQL function for that!
Example
dbadmin => CREATE OR REPLACE FUNCTION randomdate (d1 TIMESTAMP, d2 TIMESTAMP) RETURN TIMESTAMP dbadmin -> AS dbadmin -> BEGIN dbadmin -> RETURN TO_TIMESTAMP(EXTRACT(EPOCH FROM d1) + RANDOMINT(FLOOR(EXTRACT(EPOCH FROM d2) - EXTRACT(EPOCH FROM d1))::INT)); dbadmin -> END; CREATE FUNCTION dbadmin => SELECT randomdate('2018-01-01', '2018-12-31') randomdate_in_2018, randomdate(sysdate-30, sysdate) randomdate_last_30_days; randomdate_in_2018 | randomdate_last_30_days ---------------------+---------------------------- 2018-04-16 04:28:37 | 2018-01-08 12:41:14.810584 (1 row)
0