Timestamp

[Anon]

ok you might of heard my problem before but i've decided to take action. I've recently become admin for a server and i have a mysql database on it with a field call date it contain a string that discribes the date ina standard format:

Mon Jul 9 09:28:28 2001
Mon Sep 10 22:31:39 2001

but is a varchar so i can't do interesting datetime look-ups!

I need to write a script that will change this vharchar field to a datetime it doesn't matter if i have to add a new field to copy into. it just that i don't know how to write into a datetime field what format does it take? any help would be handy.

thanks

ashley

[edraynham]

You could write a script using the strtotime() function. I would suggest storing the date in the unix timestamp format.

<?

mysql_query("select * from <table>");

while ($details = mysql_fetch_array($query))
{

Convert the timestamp

$unixtime = strtotime($details["date"]);

Write values back

mysql_query(update <table> set date='$unixtime' where key='$key');

}

?>

When this has done all records, and only then, change the column type to timestamp.

NOTE: you may want to test the use of this function before operating on the live database! It does some weird things sometimes!