How can I convert a column of date strings in a mysql db to UNIX timestamps?

Question

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

did you checked [this](http://stackoverflow.com/questions/11133760/mysql-convert-datetime-to-unix-time-stamp) — , Feb 06 '13 at 07:20
yeah I did unfortunately...can I change the structure then run a query? — absentx, Feb 06 '13 at 07:21

score 1 · Accepted Answer · answered Feb 06 '13 at 07:20

1

select all rows and use strtotime for getting timestamp value and then update the rows.

answered Feb 06 '13 at 07:20

Fatih Donmez

yep...great....just need some help getting out of my one dimensional head when working with this stuff. – absentx Feb 06 '13 at 08:29

score 1 · Answer 2 · answered Feb 06 '13 at 07:22

1

If you are using php, use "strtotime()" function and update the values in data base.

answered Feb 06 '13 at 07:22

Dharmendra

that is a great and simple suggestion...I often forget that I can easily manipulate the database just by making a quick script also..thanks. "foreach date..convert that to strtotime and update!" – absentx Feb 06 '13 at 08:25

score 0 · Answer 3 · answered Feb 06 '13 at 07:22

0

SELECT UNIX_TIMESTAMP('2007-11-30 10:30:19');

answered Feb 06 '13 at 07:22

DTukans

Kerem · Answer 4 · 2013-02-06T07:33:35.603

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)

4 Answers4