2

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.

Mert Mertce
  • 1,049
  • 7
  • 34

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Really! Thank you. I did not understand what makes it to calculate datetime again with every row, but it does! I had tried similar things but it was calculating datetime only once, and updating all rows with that one value. – Mert Mertce Jun 16 '20 at 14:24
  • An update statement without a where clause updates all the rows of the table. – forpas Jun 16 '20 at 14:29