I had my query wrong for the first fifty or so entries of a database table and because of this my date field has entries like "January 5th, 2013".
Can someone help me out with a query that will take all the entries in the date field and simply convert them to unix timestamps?
Researched lots of other questions here but nothing I have found seems to address manipulating/converting a group of dates after the fact.
Thanks
select all rows and use strtotime for getting timestamp value and then update the rows.
If you are using php, use "strtotime()" function and update the values in data base.
SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');
read more here http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
PHP;
$ts = strtotime("January 5th, 2013");
echo date("Y-m-d", $ts); // 2013-01-05
SQL;
UPDATE `table` SET `date_field` = UNIX_TIMESTAMP(`date_field`);
And, if you want to change column type (I prefer INT type for performance issues);
ALTER TABLE `table` MODIFY `date_field` INT UNSIGNED NOT NULL;
But after this, you need to insert all data to this column as integer, so;
$time = time(); // or $time = strtotime("January 5th, 2013");
INSERT INTO `table` (`date_field`) VALUES ($time)
