CODEWITHSUNDEEP
mysqlrandomtime
jagku
jagku

Reputation: 309

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

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!

Upvotes: 5

Views: 5366

Answers (3)

venkatKA
venkatKA

Reputation: 2399

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;

Upvotes: 1

AK47
AK47

Reputation: 3807

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

Upvotes: 1

fancyPants
fancyPants

Reputation: 51908

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.

Upvotes: 11

Related Questions