Date format in RSS feeds
When making our own RSS feed generators, the most awkward thing is the date format in use for each item publication date. The format in question is RFC 822. When we don't use the correct date time string, our RSS feed doesn't validate. Here's how those dates look like:
Mon, 14 Feb 2011 00:09:04 +0200
Mon, 14 Feb 2011 00:09:04 GMT
We'll show how to generate those dates with some popular web programming languages.
PHP
In PHP the most natural way is to use date() function with suitable formatting parameter. When reviewing PHP documentation you'll find a convenient constant that defines just what we need - DATE_RSS. Also don't forget to set explicitly the correct time zone and don't rely on web server's default one. This can be achieved using date_default_timezone_set(). And here's a sample fragment assembled:
date_default_timezone_set('Europe/Sofia');
echo '<pubDate>' . date(DATE_RSS) . '</pubDate>;
MySQL
MySQL has built-in function for formatting date and time values - DATE_FORMAT. Let's have a table defined as posts(title varchar(80), date_created datetime). Suitable query for extracting correctly formatted date is:
SELECT title, DATE_FORMAT(date_created,'%a, %d %b %Y %T') AS rss_date_created
FROM posts ORDER BY date_created DESC LIMIT 10
This returns the last 10 posts ordered by date.
PostgreSQL
In case of PostgreSQL we use to_char() for converting date and time values to text string. There's a little catch however - there are two built-in types for storing date values: timestamp with time zone and timestamp without time zone. In the former case our table definition becomes posts(title varchar(80), date_created timestamp with time zone). Just use to correctly assembled formatting string to build the correct query:
SELECT title, to_char(date_created, 'Dy, DD Mon YYYY HH24:MI:SS TZ') AS rss_date_created
FROM posts ORDER BY date_created DESC LIMIT 10;
When timezone information is not stored along the data itself we need to add it manually. For table posts(title varchar(80), date_created timestamp without time zone) we choose GMT zone. Then convert all time values in the selected zone and modify the query above:
SELECT title, to_char(dt::timestamptz AT TIME ZONE 'GMT', 'Dy, DD Mon YYYY HH24:MI:SS GMT') AS rss_date_created
FROM posts ORDER BY date_created DESC LIMIT 10;
No comments yet
This page was last modified on 2024-04-19 10:10:07