I'm retrieving tweets trhough the Twitter Search API and keeping them in MySql database. The problem is this server hasn't installed the PHP DateTime y DateTimeZone libraries (or so I was told) so this method does not work:
$datetime = new DateTime($tw_result["created_at"]);
$datetime->setTimezone(new DateTimeZone($DATETIME_ZONE));
$fecha_tweet = $datetime->format('Y-m-d H:i:s');
So, do you know other straight way to parse this "Wed, 08 Feb 2012 23:59:50 +0000" into 2012-02-08 23:59:50 or a valid date for MySql? (other than slicing with substrings'). I tryed several ways of using gmdate() but didn't get to it.
Thank you
echo gmdate('Y-m-d H:i:s', strtotime('Wed, 08 Feb 2012 23:59:50 +0000'));
Twitter created_at to MySql datetime query example.
This query works for me. First split the twitter date format in to strings then concatenate and convert to datetime all in MySql:
SELECT
STR_TO_DATE(CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(dt, ' ', 3), ' ', - 1),
'/',
SUBSTRING_INDEX(SUBSTRING_INDEX(dt, ' ', 2), ' ', - 1),
'/',
SUBSTRING_INDEX(SUBSTRING_INDEX(dt, ' ', 6), ' ', - 1),
' ',
SUBSTRING_INDEX(SUBSTRING_INDEX(dt, ' ', 4), ' ', - 1)),
'%e/%b/%Y %r') dtm
FROM
tweets;