Page 1 of 1
Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 7:56 am
by ourmaninparis
Hello,
I've created an RSS feed using PHP. All works correctly except that I cannot get the date to display. In the MySQL database the date is in format :
YYYY-MM-DD HH:ii:SS
The field is format DATETIME.
Here is a sample of my file:
Code: Select all
for($i=0;$i<20; $i++) {
$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');
echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';
Any suggestions for getting this to display?
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 8:56 am
by ourmaninparis
Just tried this but it isn't working:
Code: Select all
for($i=0;$i<20; $i++) {
$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');
echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>date("r", strtotime($DATA[.$pubdate.]))</pubDate>
</item>
';
} //end of the for-loop
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 9:25 am
by Christopher
You did not show your SQL. I'd recommend using DATE_FORMAT() in your query and letting MySQL format the date.
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 9:32 am
by x_mutatis_mutandis_x
Code: Select all
for($i=0;$i<20; $i++) {
$subject = mysql_result($result,$i,'t1.Notice_Title');
$description = mysql_result($result,$i,'t1.Notice_Text');
// Clean the description
$description = str_replace ("&","",htmlspecialchars(strip_tags($description)));
$link = mysql_result($result,$i,'t1.Notice_Link');
//to record when the feed was published
$pubdate = mysql_result($result,$i,'t2.publish_up');
//format the date
$format = 'm/d/Y'; //or what ever format you choose (see the reference below)
$pubdate = date_format(date_create($pubdate), $format);
echo '
<item>
<title>'.$subject.'</title>
<link>'.$link.'</link>
<description>'.$description.'</description>
<pubDate>'.$pubdate.'</pubDate>
</item>
';
} //end of the for-loop
For reference:
http://www.php.net/manual/en/function.date.php
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 11:06 am
by ourmaninparis
Works a treat! Thanks very much

Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 1:55 pm
by tr0gd0rr
Oh gosh don't format dates in MySQL!
Four reasons off the top of my head:
- If you ever need to change to SQL Server or Oracle or whatever you have to rewrite queries.
- The designer or html writer should have control over date format. The choice of date format is largely an aesthetic one.
- If you want to internationalize your app, you may want different date formats for different locales.
- You may want to let users choose the date format.
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 5:00 pm
by pickle
2), 3) ,4) are invalid in this instance because this is for an RSS reader, so the date has to be in the RSS accepted standard format. 1) is a valid concern
Re: Convert date to display in RSS readers
Posted: Thu Apr 26, 2012 11:01 pm
by Christopher
I don't think 1) is much of a reason either. People rarely, if ever, convert from one database to another. And when you do you have to review all the SQL anyway and are sure to catch any functions used. I agree that 2), 3) ,4), which are all about pushing the date format into the template are very valid reasons if there is a need for creativity in date formatting. Usually app centralize this kind of formatting so it can be changed site-wide with a setting and not have to dig through templates, especially for 3).
Re: Convert date to display in RSS readers
Posted: Fri Apr 27, 2012 12:16 pm
by x_mutatis_mutandis_x
Christopher wrote:I don't think 1) is much of a reason either. People rarely, if ever, convert from one database to another. And when you do you have to review all the SQL anyway and are sure to catch any functions used. I agree that 2), 3) ,4), which are all about pushing the date format into the template are very valid reasons if there is a need for creativity in date formatting. Usually app centralize this kind of formatting so it can be changed site-wide with a setting and not have to dig through templates, especially for 3).
And especially if you use an ORM, then they have abstracted SQL (Hibernate HQL, Doctrine DQL etc) accross platforms as well in which case 1) is not a reason at all
2), 3), 4) could be a reason if your building a UI app but I aggree with pickle, when it's a standard-acceptable format.
Re: Convert date to display in RSS readers
Posted: Fri Apr 27, 2012 2:26 pm
by tr0gd0rr
Yes, definitely 2,3,4 are irrelevant in this instance. As far as 1, after I spent several painful months to convert a whole app to work with both MySQL and SQL Server, I made it a rule to always use standard SQL that works across platforms. And yes, when you use an ORM, that becomes a lot easier.
I would also add a #5: separation of concerns. The code that interacts with the database should know nothing about how the data is presented. You may have many display adapters for the same exact data (e.g. mobile html, json api, xml api, xls, etc.) Now of course you will probably use strtotime on the data sometime after you get it from the database (so it doesn't hurt anything that the database is formatting the date) but the concept of separating concerns is important.
I just wanted to make a point to any noobs coming across Christopher's comment that you shouldn't interpret his suggestion as a general best practice to format dates at the database level.