Change a date format and it's values in the DB

[Sonic_Rage]

Ok, I have 97 rows of news articles that have a column called 'date' and the format is yyyy-mm-dd. Now, seeing the errors of my ways, I need to update that column to now display the dates in the mktime format of 1071016199 etc...

I know I can change the field to Bigint(20) to handle it, but how can I update all of the original dates to now show the new date format?

I'm not sure how to go about creating a query that can convert the dates in the DB. Is it possible? or is this something where I will have to manually insert the timestamp equivalent in to each date field?

Help! 😕

[LordShryku]

alter the table to add a new field to house the new value.

Then make a small php script to do it for you.

 //connect, blah blah, select db, blah
$sql = "SELECT unique_id, 
               date_field
          FROM table";

$res = mysql_query($sql);
while($row=mysql_fetch_array($res)) {
   mysql_query("UPDATE table
                   SET new_field = '".strtotime($row['date_field'])."'
                 WHERE unique_id = '".$row['unique_id']."'");
}

Then drop the existing date field. Alter the new field's name to what the old ield was named.

[Sonic_Rage]

Oh very cool, thank you so much. That makes total sense.