Get MySQL date in RFC-822 format for RSS feeds

Posted by Quinn McHenry in 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

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.

6 Responses to “Get MySQL date in RFC-822 format for RSS feeds”

June 04, 2009 at 7:40 am, Damian said:

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

Reply
July 27, 2009 at 6:33 am, Jannick Bolten said:

Thank you! I’m using it

Reply
August 21, 2009 at 12:07 pm, Marcello Orizi said:

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

}

Reply
February 19, 2010 at 11:21 am, Daniel said:

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

Reply
June 13, 2011 at 3:44 pm, Anonymous said:

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.

Reply
January 18, 2012 at 10:03 am, James said:

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.

Reply