mysql - how to get a random time (not date) between two time ranges

Question

I am trying to work out how to return a random time between 2 time ranges. For example, get a random time between 15:00:00 and 22:00:00

So far, I have - but don't know how to limit it

select sec_to_time(floor(15 + (rand() * 86401)))

Any help would be much appreciated!

Answer 1

SELECT SEC_TO_TIME(
          FLOOR(
             TIME_TO_SEC('15:00:00') + RAND() * (
                  TIME_TO_SEC(TIMEDIFF('22:00:00', '15:00:00'))
             )
          )
        );

Calculate in seconds. Then add a random number that is in the range of seconds between 15:00:00 and 22:00:00 to 15:00:00. Then convert the seconds back to a time value.

• for more information on the used functions, see this link.