Formatting dates for mysql

[defeated]

Hi,

Whenever it comes to dates I go blank and just hear/read "blah blah blah".  I'm sure there's a name for a condition like that but it's causing me a problem right now.

 

I am making a RSS feed aggregator, inserting RSS feed posts into a mysql db and want to be able to extract those posts in order by date. (so that I can create a new feed of the last posts of multiple rss feeds)

 

My problem is that the pubDate format for feeds is Fri, 29 Oct 2010 11:22:58 +0000 and that doesn't work with mysql.

 

Apart from having to reformat the date I have to take into consideration the +0000 part could be different depending on where in the world the feed was published.

 

Any pointers would be gratefully received!

[litebearer]

A rough start...

 

<?PHP
$main_date = "Fri, 29 Oct 2010 11:22:58 +0000";
$my_array = explode("+", $main_date);
$front_date = $my_array[0];
$back_date = $my_array[1];
$new_date = date("Y-m-d", strtotime($front_date));
echo $new_date;
?>

[defeated]

Thanks,

In the end I went for

$pubDate = mysql_real_escape_string($pubDate);
	$pubDate = date("Y-m-d  H:i:s", strtotime($pubDate));

which I doubt does what I was asking for (taking into account time zones), but at least it works.

 

If anybody can tell me if it will take time zones into consideration I'd be grateful.

[Psycho]

No need to go remove the "+0000". That "string" can be easily converted to a timestamp using strtotime().

 

The +0000 is important and is used by strtotime() to convert the time based upon Greeenwich Mean Time (GMT) or UTC depending on what you like to call it. But, strtotime() will normalize the time according to the timezone of the server.

 

If you remove the +0000 then the time is converted as if it was based in your timezone - which it isn't. So, removing the +0000 will result in the time being off by however many hours difference between GMT and the server's timezone:

echo date('m-d-Y H:i:s', strtotime('Fri, 29 Oct 2010 11:22:58 +0000'));

 

For me, that outputs 10-29-2010 06:22:58 because on October 29 there was a five hour difference between my timezone (Central Time) and GMT. However, if I ran that for today's date, there would be a 6 hour difference because we moved our clocks back one hour a week ago from daylight savings time to standard time.

[simshaun]

strtotime does take into account the timezone modifier.  I do see a problem though..  you lose track of the offset when storing the date into MySQL.  When you go to re-create a feed, you'll have no idea what the timezone offset was.  I suggest storing the entire pubDate value in a varchar column and storing a UNIX timestamp representation of it in an INT column.  You could then sort by the int column but still have the original RFC822 format for generating a feed.

[defeated]

Thank you for clearing that up.  Delighted that strtotime() takes into account time zones.

 

The storage will not be a problem because the values are only stored in the db long enough to create a new rss feed of the last 5 posts from all aggregated feeds (select * from table order by pubDate desc).

 

I have a feed reader that posts to twitter.  When it calls my aggregation page it calls it as feed_whatever.xml and I use mod rewrite in .htaccess to call aggregate.php?id=whatever

 

Then the page reads all feeds associated with 'whatever' from a table, puts the contents of those feeds into a temp table and calls out the last 5 entries ordered by date (thus an aggregated feed).

 

It then places those entries into xml format as a new feed.  It reads that feed and posts any new entries to twitter.