how to generate random unix timestamps mysql

Question

I am trying to generate random unix timestamps and only found this example:

SELECT from_unixtime(
       unix_timestamp('2008-01-01 01:00:00')+floor(rand()*31536000)
);

This works for generating a random time, but it reconverts the time into this format 2008-06-16 14:14:38 which is not the format I want. I want the result to be a unix time stamp, for example 1357597694 in UTC-5.

All I want to do is be able to tell it how many unix timestamps to spit out and have the result be in unixtime stamp form. That's all. I don't see why there isn't a post about this already.

EDITED ok salman A

ok lets say i need to pick a random date between 1354439504 & 1357599297 how would i do that in mysql script

Answer 1

1 is a UNIX timestamp. So is 2. So is 123456789. UNIX timestamp is just an integer; and the rules for generating random integers can be used. In MySQL you could simply write the following to generate a random integer in the range [0, 9]:

SELECT FLOOR(RAND() * 10)

The above can be modified to generate random numbers in the range [min, max] as follows:

SELECT 1354439504 + FLOOR(RAND() * (1357599297 - 1354439504 + 1))

To generate 1000 random timestamps using a query, use a table, any table, that has many rows:

SELECT 1354439504 + FLOOR(RAND() * (1357599297 - 1354439504 + 1))
FROM table_with_1000plus_rows
LIMIT 1000

Answer 2

If you wanted a unix timestamp, then just skip the from_unixtime:

select unix_timestamp('2008-01-01 01:00:00')+floor(rand()*31536000)