CODEWITHSUNDEEP
mysqlsqlrandomtimestampsql-update
Stanislas Piotrowski
Stanislas Piotrowski

Reputation: 2694

sql update random between two dates

I've tried to update my database and changing dates. I've done some research but I did not found any issue. So I used two timestamp.

I've tried to do that method:

UPDATE `ps_blog_post` 
SET `time_add` = ROUND((RAND() * (1387888821-1357562421)+1357562421))

Now everywhere the new date is:

0000:00:00

Anykind of help will be much appreciated

Upvotes: 7

Views: 10266

Answers (3)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Try this one to get timestamp between two timestamps 

SET @MIN = '2013-01-07 00:00:00';
SET @MAX = '2013-12-24 00:00:00';

UPDATE `ps_blog_post` 
SET `time_add` =  TIMESTAMPADD(SECOND, FLOOR(RAND() * TIMESTAMPDIFF(SECOND, @MIN, @MAX)), @MIN);

Fiddle

Upvotes: 23

user3086879
user3086879

Reputation: 41

Try this:

select DATEADD(SECOND, - Round(259200 * RAND(), 0), getdate())

I state the current data and the you substract seconds from today.. The example take a random date from today and minus 30 days (60*60*24*30)...

Upvotes: 4

Mureinik
Mureinik

Reputation: 311053

You have the right idea, your conversion from the int literals you're using back to the timestamp seems off though - you're missing an explicit call to FROM_UNIXTIME:

UPDATE `ps_blog_post` 
SET `time_add` = 
     FROM_UNIXTIME(ROUND((RAND() * (1387888821 - 1357562421) + 1357562421)))

Upvotes: 7

Related Questions