5

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!

jagku
  • 309
  • 3
  • 14

3 Answers3

11
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.
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • I know it's been 2.5 years since you posted this answer, but you managed to solve a problem I couldn't figure out on my own. Thanks @fancyPants ! – Andrew Brēza Apr 10 '17 at 21:05
1

Try this way

select FROM_UNIXTIME(UNIX_TIMESTAMP('<starting_date_in_date_format>') + FLOOR(0 + (RAND() * UNIX_TIMESTAMP('<ending_date_in_date_format>'))) from dual;
Kaushik
  • 6,150
  • 5
  • 39
  • 54
venkatKA
  • 2,399
  • 1
  • 18
  • 22
  • Shouldn't it be `select FROM_UNIXTIME(UNIX_TIMESTAMP('') + FLOOR(RAND() * (UNIX_TIMESTAMP('') - UNIX_TIMESTAMP('')))` because rand should be multiplied by distance between unix ending and starting timestamp but not unix ending timestamp itself. Unix timestamp is just an integer so we are basically generating random number from range: `range_start + floor(rand() * (range_end - range_start))`. – AurimasLazdauskas May 06 '20 at 05:26
1

Logic is simple,

  1. Get difference between 2 times.
  2. Generate random number between those 2 times(numbers)
  3. You can direct generate time in Minutes or Seconds.

Following is example for getting Random Hour,

select Round((FLOOR(RAND() * (TIME_TO_SEC('15:00:00') - TIME_TO_SEC('22:00:00') + 1)) + TIME_TO_SEC('22:00:00')) / 3600,0)

EDIT : Fiddle

For reference see this link

Community
  • 1
  • 1
AK47
  • 3,707
  • 3
  • 17
  • 36