Extract all records from a table and insert into second table 1

[4x4uk]

I've been using php for a while but just started using mysql. In my haste I created a table which stores a timestamp in a varchar field (01/Jan/2003 hh:mm:ss)so they would be in the format I wanted(01/Jan/2003 hh:mm:ss). What I should have done was store dates as a datetime field and converted them afterwards whith php. That's not really a problem but I would like to keep the existing data. My thought was to create a new table with the date field as a datetime field. Then run a query extracting all records from the existing table, manipulate the date with php and put the results in the new table.
Is theis the best way of correcting my earlier mistake or is there an easier way.
thanks
Ian It's not a lie if you believe it!

http://www.ne4x4.org.uk/

http://www.ne4x4.org.uk/Ian/

http://www.ne4x4.fsnet.co.uk/beck/

 

[sleipnir214]

Yeah, never confuse data with presentation formatting.

You don't need to create the other table.

Create a new column of type datetime in the table. Pull out each old date string (and another column which delineates a row), convert it to the correct format, and update the new column with the new datetime value. Once you have verified that the all the data was inserted correctly in the new column, drop the old column and rename the new column to the old column's name.

Given your format, something like the following should convert your dates:

<?php
$themonths = array('Jan', 'Feb', 'Mar', 'Apr', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

function rearrange_date ($thedate)
{
	global $themonths;
	
	$foo = preg_split ('/\/|:| /', $thedate);

	$retval = $foo[2] .'-'. sprintf ('%02d', array_search($foo[1], $themonths) + 1) .'-'. sprintf ('%02d', $foo[0]) .' '. $foo[3] .':'. $foo[4] .':'. $foo[5];
	
	return $retval;
}

/* The next two lines exist to test drive the function
$thedate = &quot;01/Mar/2003 13:25:00&quot;;
print rearrange_date ($thedate);
*/
?>

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[4x4uk]

Thanks
Ian It's not a lie if you believe it!

http://www.ne4x4.org.uk/

http://www.ne4x4.org.uk/Ian/

http://www.ne4x4.fsnet.co.uk/beck/