8

How can I change the date by the random number of days in PostgreSQL?

Unfortunately Generate a random number in the range 1 - 10 solution with trunc doesn't work:

select date(now()) + (trunc(random()  * 20))

results in: 

ERROR:  operator does not exist: date + double precision
LÍNEA 1: select date(now()) + (trunc(random()  * 20))
Community
  • 1
  • 1
Bogdan Gusiev
  • 7,767
  • 14
  • 60
  • 77

2 Answers2

23
select date(now() + trunc(random()  * 20) * '1 day'::interval);

Any existing date column can also be used in place of now()

See: https://www.postgresql.org/docs/current/functions-datetime.html

a_horse_with_no_name
  • 497,550
  • 91
  • 775
  • 843
Milen A. Radev
  • 57,132
  • 21
  • 101
  • 108
4

How random? For example - if you want random data from last year:

select cast( now() - '1 year'::interval * random()  as date );