What I'm attempting to do: within SQL, I'm trying to assign a randomized UNIX timestamp within specific range of hours of the last 24h.
What I got so far is this:
Step 1 with "0" placeholder - making sure the hour of unix timestamp is changed only for those whose random hour wasn't assigned this day.
UPDATE users SET random_hour = 0 WHERE random_hour > (CURRENT_TIMESTAMP() - 86400);
Step 2: Made the query actually generate a random timestamp within last 24h.
UPDATE users SET random_hour = UNIX_TIMESTAMP((CURRENT_TIMESTAMP() - INTERVAL FLOOR(RAND() * 24 * 60 * 60) SECOND)) WHERE random_hour > (CURRENT_TIMESTAMP() - 86400);
Step 3: Make sure the hours assigned are between 8:00-16:00 GMT.
This is where I'm failing.
Limitations I'm facing are:
- it has to be unix timestamp (other columns and scripts depend on all times being in unix timestamps)
- this is a task that runs frequently and assigns users the random hour, that's why there's a check for
random_hour > current_timestamp() - 86400, so that it's stable, and that's also why I can't just work on the current day's timestamp range to do it, - database i'm running is MariaDB 10