The question asked here is
Update every row with a random datetime between two dates
to update with the same datetime.
What I need is to update every row with a different random datetime within a range.
With:
strftime('%s', enddate) - strftime('%s', startdate)
you can get the difference in seconds between 2 datetimes.
With:
abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1))
you can get a random integer, greater or equal to 0
, that is less than or equal to the difference between the 2 datetimes in seconds.
What you can do is add this random number of seconds to the starting date of your range to create a random datetime within that range:
update tablename
set datecol = datetime(
startdate,
abs(random() % (strftime('%s', enddate) - strftime('%s', startdate) + 1)) || ' second'
)
datecol
is the date column in your table
startdate
and enddate
are the boundaries of your range.