Get MySQL date in RFC-822 format for RSS feeds

Contributed by qmchenry  
Tagged: MySQL  

• Tweet

Browse to next tech-recipes article

To get the PubDate element of an RSS feed to validate, it needs to be in RFC-822 format. MySQL has flexible ways of working with dates and times which make this a simple task.

Given a datetime column called pubdate, this select statement (which can be combined with other selections) will yield a column of dates named rfcpubdate formated in RFC-822 format:

SELECT DATE_FORMAT(pubdate,'%a, %d %b %Y %T') AS rfcpubdate FROM tablename WHERE 1


The full RFC-822 date includes a timezone value which is not included in the MySQL output. If using PHP, this value can be appended to the output (assuming a variable $rfcpubdate exists containing a date string from the above select statment) using:

echo " $rfcpubdate ".date('T')."";


This will yield output that looks like:

Mon, 19 Jun 2006 07:41:18 PDT

You may also like -

Subscribe to Our RSS/XML Feeds to Keep Up with All Our UpdatesPrograms and Websites to Help You with RSS FeedsHow to Add a Webfeed (RSS/XML) Feed in WindowsGet Your Weather Through RSS/XML FeedsOpera Mini 3.0: How To Manage RSS FeedsOutlook 2007: Remove or Disable RSS Feed SupportFirefox 2 - Change the Feed ReaderIE7: How to Subscribe to RSS Feed

 

6 Comments -

1. Damian said on June 4, 2009

   Found this very helpful. The only one thing I would say is that if you try and use ORDERBY rfcpubate DESC in the MYSQL query something weird happens and it doesn’t select the most recent. To fix i did this:

   SELECT pubdate, DATE_FORMAT(pubdate,’%a, %d %b %Y %T’) AS rfcpubdate FROM tablename ORDERBY pubdate DESC

   very helpful indeed though

2. Jannick Bolten said on July 27, 2009

   Thank you! I’m using it :-)

3. Marcello Orizi said on August 21, 2009

   Hi all,
   to add the correct timezone formatted in RFC-822 format in Java I found this method. I don’t like it too much because it requires to call cal.getTime() everytime but it seems to do what needed.

   import java.util.Calendar;
   import java.text.SimpleDateFormat;

   public class foo{
   public static final String DATE_FORMAT_RFC822_TIMEZONE = “Z”;

   public String getRFC822TimeZone() {
   Calendar cal = Calendar.getInstance();
   SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT_RFC822_TIMEZONE);
   return sdf.format(cal.getTime());
   }

   }

4. Daniel said on February 19, 2010

   Thanks, i’ve searched for that all day long!

5. Anonymous said on June 13, 2011

   How do you take a column that has dates stored as RFC-822 and pull it out as YYYYMMDD or of the like? That so far seems impossible.

6. James said on January 18, 2012

   Just wanted to mention that you can simply include the timezone offset in the format string, assuming it is relatively static. So for example:

   DATE_FORMAT(date, ‘%a, %d %b %Y %T -0600′)

   The -0600 portion does not contain any special or escaped characters, so it just displays as written. Depending on your application environment, there are a number of ways to dynamically produce this format string prior to building the select statement.

RSS feed for comments on this post. TrackBack URL

Leave a comment -