sqlsqlitesqlitestudio

update every row with a different random datetime of a range


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.


Solution

  • 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.