Get MySQL date in RFC-822 format for RSS feeds

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

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

  • Damian

    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

  • http://twitter.com/JannickBolten Jannick Bolten

    Thank you! I’m using it :-)

  • http://twitter.com/marcelloorizi Marcello Orizi

    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());
    }

    }

  • http://www.escodent.de/ Daniel

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

  • Anonymous

    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.

  • http://www.backlightsw.com James

    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.