processing date and time information with php and sql

Im building a program that grabs date and time inform开发者_开发技巧ation from tweets through the twitter api, my challenge right now is I need to figure out what the best plan is to go about storing a timestamp like this: Fri Jul 16 16:55:52 +0000 2010, into a mysql database and then after they are stored making an sql call to the database ordering them by the most recent date

You will want to make the data type on your MySQL field a DATETIME. To insert you need to read the timestamp from Twitter into a PHP -readable timestamp with strtotime(), then convert it to the mysql format with the date() function.

For Example

$mysqlDate = date('Y-m-d H:i:s', strtotime($dateFromTwitter));

Turn the date into an valid mysql datetime

date('Y-m-d h:i:s', strtotime("Fri Jul 16 16:55:52 +0000 2010"));

That then needs quoting and inserting into your table:

$sql = "insert into tweets (tw,daydate) values ('$tweet', '$date');"

Where daydate is a DATETIME field.

Using PHP's strtotime() is surely a nice way of doing this but I've found issues working with strtotime() earlier. So perhaps you may like to do this only in MySQL, like:

SELECT STR_TO_DATE(
    CONCAT(
        SUBSTRING('Fri Jul 16 16:55:52 +0000 2010', 1, 20),
        ' ',
        SUBSTRING('Fri Jul 16 16:55:52 +0000 2010', -4)
    ),
'%a %b %d %H:%i:%s %Y');

Please note that when doing so I've ignored the +0000 part of the time-stamp.

Hope this helps.