Convert Written Date to Unix Timestamp

opfor

I have over 200 user records in my MySQL db with a field "user_regdate" in a string, formatted like "April 2, 2002"

I recently changed the user registration process (from PHPnuke to phpBB - and manually converted the user data), which now inputs the "user_regdate" as a Unix Timestamp.

In order to display my users in order of registration date, I need to convert the old dates into a Unix Timestamp.

How can I, preferably with a MySQL query, alter these records to change "April 2, 2002" into "1230912409(or whatever the actual unix timestamp becomes)"?

I swear I searched for this in the forums, but only found solutions that go in the other direction. I thank you all in advance, and hope someone out there has the expertise to help.

LordShryku

Well, I don't know that MySQL will be able to handle that casting/converting. Though, php will have no problem with it using [man]strtotime[/man]

opfor

Thanks, man - I will investigate that function.

Perhaps I can use it to create a PHP script to do the pull from the db, convert, then reinsert.

onion2k

Why do people store dates as strings in databases? Theres a Date, Datetime, and a timestamp data type for a reason.

That reason being... the mysql Date functions.