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!

fancyPants · Accepted Answer · 2014-09-12 10:53:53Z

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.

answered Sep 12, 2014 at 10:53

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Brēza Over a year ago

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 !

Community · Accepted Answer · 2017-05-23 12:25:33Z

1

Logic is simple,

Get difference between 2 times.
Generate random number between those 2 times(numbers)
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

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered Sep 12, 2014 at 10:58

AK47

3,8073 gold badges21 silver badges37 bronze badges

Comments

Kaushik · Accepted Answer · 2014-09-12 11:38:54Z

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;

edited Sep 12, 2014 at 11:38

Kaushik

6,1625 gold badges41 silver badges56 bronze badges

answered Sep 12, 2014 at 10:55

venkatKA

2,3991 gold badge20 silver badges22 bronze badges

1 Comment

AurimasLazdauskas Over a year ago

Shouldn't it be

select FROM_UNIXTIME(UNIX_TIMESTAMP('<starting_date_in_date_format>') + FLOOR(RAND() * (UNIX_TIMESTAMP('<ending_date_in_date_format>') - UNIX_TIMESTAMP('<starting_date_in_date_format>')))

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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Linked

Related