Get MySQL date in RFC-822 format for RSS feeds

Contributor Icon Contributed by qmchenry Date Icon June 19, 2006  
Tag Icon Tagged: MySQL

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

Previous recipe | Next recipe |
 
  • Anonymous
    Don't use the php DATE('T') if your (tally ho chaps) british, in winter your feed will validate and general work fine, however in sping when day light savings time kicks in it will mysteriously break. PHP pust your timezone as BST or "British Summer Time" which will not validate, where as GMT (grewnich mean time) does.

    You have no idea how long it too me to twig what was wrong...

    Tom
  • 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
  • Thank you! I'm using it :-)
  • 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());
    }

    }
blog comments powered by Disqus