How to update/Insert random dates in SQL within a

Question

Please forgive me. I am an absolute newbie and I need help with this table in phpmyadmin

My Table has the following columns:

Primary_ID, Begin_Date, End_Date, Timestamp

How do I update in phpmyadmin, selected rows with randomly generated begin_dates and timestamp within a specified date range (eg: 30 days in a month). E.g of desired outcome

Primary_id--- Begin_Date -------------Timestamp

1.------------2008-09-02--------------2008-09-02 21:48:09

2.------------2008-09-03--------------2008-09-03 15:19:01

3.------------2008-09-14--------------2008-09-14 01:23:12

4.------------2008-09-27--------------2008-09-27 19:03:59

Date Range between 2008-09-01 and 2008-09-31. Time is variable 24 hrs

I am a newbie, so a syntax that will work in phpmyadmin will help greatly.

We are making a presentation for a gym site with 500 members but the added member values all have the same begin date and time. Trying to separate them into different monthly registrations in the database, eg 50 people registered in August at different days and times, 35 people in October, etc. Hope it is clearer now. Thanks –

When I try one of the below answers, I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$randomDate = rand(1,31)' at line 1. So ideally, a code I can copy and paste into phpmyadmin with minimal editing will be appreciated. In sequence if possible. For a total dummy to understand and execute.

Answer 1

something like:

insert into myTable (begin_date) values date_add('2008-09-01', INTERVAL RAND()*30 DAY)

that should create a new row with a random begin_date

update myTable set Timestamp = date_add(begin_date, INTERVAL RAND()*1440 MINUTE)

then that one should set the timestamp to a random minute of that day.

Answer 2

I'd start with something like this. A bunch of these can be combined, but I split it up so you can see what I'm doing.

To get random numbers, you can use rand(). Get one for the date, hour, minute, and second

$randomDate = rand(1,31);
$randomHour = rand(1,24);
$randomMinute = rand(0,59);
$randomSecond = rand(0,59);

You will want leading zeros (03 instead of 3) so you can use str_pad to add them, if required

$randomDate = str_pad($randomDate, 2, '0',STR_PAD_LEFT);
//The '2' is how many characters you want total
//The '0' is what will be added to the left if the value is short a character

Do the same with all your other random values. Just because I like neat queries, you should make up your final update strings next.

$newDate = '2008-09-'.$randomDate;
$newTime = $randomHour.':'.$randomMinute.':'.$randomSecond;

Now I don't know how you're determining which rows you want to update, so I will leave that up to you. For an example, I will show you a query if you wanted to do this with Primary_id 3:

$x = mysql_query("UPDATE yourTable SET Begin_Date=\"$newDate\", Timestamp=\"$newTime\" WHERE Primary_id = 3");