Free unix timestamp and epoch conversion tool

I had to find the way to convert a human date to epoch (importing content from a man made DB into modx_site_content), and I really found this website pretty handy :

http://www.epochconverter.com/

I thought I’d share, though my "problem" is not really solved, since the DB I am importing from has dates reading [tt]2001-01-15[/tt] for example and I would need [tt]YYYY-MM-DD HH:MM:SS[/tt] to use the [tt]SELECT unix_timestamp[/tt] from MySQL to import those items with a proper createdon value... any idea ?

Can’t you just use strtotime?

Thanks ZAP

I was trying to import this DB to the MODx DB directly with MySQL, didn’t think I had to go for a PHP script but I’ll give this a look...

Quote from: davidm at Dec 04, 2007, 11:58 AM

Thanks ZAP

I was trying to import this DB to the MODx DB directly with MySQL, didn’t think I had to go for a PHP script but I’ll give this a look...

Aha. That makes sense. I don’t know enough MySQL to tell you how to do it that way, but I expect there’s a similar function.

Alternatively, you could just make a new column in the table, use PHP to fill it with the strtotime value of the date, and then import directly in MySQL using that field (taking care not to mess up the indexes)...

You’ve reminded me that I need to learn a lot more MySQL!

Yeah well, I have looked this up and found the MySQL to make this work
It’s not that hard.

I have created a new field named timestamp and then I just needed to update the timestamp field with the unix timestamp value based on the date field

[tt]UPDATE `table_name` SET `timestamp`=unix_timestamp(`date`) [/tt]

Given the way dates were recorded, I had to do a little search and replace to change the date format I had [tt]2001-01-15[/tt] into the proper one [tt]2001-01-15 00:00:00[/tt] (since I had no hours, minutes and second, I choose to set all to midnight). For some reason it didn’t work with the short date (documentation seems to indicate it should work but it didn’t...).