Get MySQL date in RFC-822 format for RSS feeds

Contributed by qmchenry

June 19, 2006

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 |

Viewing 1 Comment

Anonymous 4 months ago 1 point
Please login to rate.

Do you already have an account? Log in and claim this comment.

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

reply edit reblog flag