Sort by date help

[ferret147]

as you will already know when you store the date in a database it is like this 0000-00-00 00:00:00

 

What I am doing is storing blog (wordpress) post and Tweets into a database and saving the titles, description and pubDate data from the RSS feeds.

 

My problem is that the dates in the RSS feeds are formatted like this - Fri, 27 Aug 2010 11:37:28 +0000 so when I try to display this data on a php page date and time posted I can not.

 

Is there a way to use the format Fri, 27 Aug 2010 11:37:28 +0000 and sort it by date and time posted when retrieving form the database or am I going to have to do some coding to format Fri, 27 Aug 2010 11:37:28 +0000 onto 0000-00-00 00:00:00 when initially entering the data into the database ?

 

Any advice would be greatly appreciated.

[Pikachu2000]

You should be storing the dates in the database, in the proper format, with the correct field data type to begin with.

[ferret147]

Indeed this is what I am trying to achieve, have a look at the URL's I have put below, you will have to view the source code and find the pubDate of each item.

 

Wordpress example - http://bestblog.wordpress.com/feed/

Twitter Example - http://twitter.com/statuses/user_timeline/14230524.rss

 

My script gets the contents of 20 blogs and 20 Tweeters every 5 minutes and puts them all into a database and I want to be able to then display these database entries sorted by the time and date they were originally posted.

 

So if Twitter and Wordpress do not format the dates properly in the first place how am I to sort then !!!

 

It either has to be before I inject into my database or on retrieval when I put the results into my php page but which one and how!

 

I knew this was going to be a tricky one when I started but did not realise it would be this difficuly to reformat a date

[Pikachu2000]

The field's data type should be DATETIME.

 

As far as formatting it, for the example you gave (assuming you're using PHP 5.2 or greater), this would format the date correctly.

 

<?php
$date = 'Fri, 27 Aug 2010 11:37:28 +0000';
$formatted = date_format(date_create($date), 'Y-m-d H:i:s');
echo $formatted;
// RETURNS '2010-08-27 11:37:28'
?>

[ferret147]

That looks like the way to do it thanks, I did not know of this method but it is not formatting the date correctly!

 

Source - Sat, 23 Jan 2010 13:16:26 +0000

Is out putting - 2010-09-23 01:13:45

 

So the month is wrong and the time !!!

[Pikachu2000]

Not sure why that's happening to you. I pasted in your date locally and it formatted properly, and I uploaded the script to one of my hosing accounts, and it formatted properly there as well.

[ferret147]

I just done exactly the same and it worked ok also !!!

 

Very strange, there must be something else in my script which is effecting it, thanks for your help, most appreciated.

 

I will mark this as resolved as the code snippet you supplied did resolve the problem.

 

Thanks