We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Generating a Random Date — Vertica Forum

Generating a Random Date

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)
Sign In or Register to comment.