How to deal with dates and timestamps in WordPress

If I hadn’t moved across the country recently, I probably never would have discovered a bug in how FeedWordPress handles the timestamps of syndicated posts. Fortunately, I did, and this bug is fixed in the recently-released version 0.99.

To explain what I mean, I’ll have to back up a bit, first.

There are a couple things that FeedWordPress uses date and time information for:

  1. Dating new posts: When a new post comes in over a feed, FeedWordPress uses the date and time information reported by feeds to date posts appropriately in the WordPress database. This means parsing date and time information and putting the resulting timestamps into the database.

  2. Checking for updates to existing posts: After a post has been imported into the WordPress database, FeedWordPress will keep checking to see whether the feed reports any updates to that post. This means comparing the last-updated timestamp on the feed to the last-updated timestamp in the database to see which version is newer.

The problem that I noticed came about because FeedWordPress was trying to do something sensible and easy to handle time zones when it was doing all this. Date/time handling in WordPress is fairly easy, once you understand what to do, but it is certainly anything but sensible, and therein lies the problem.

Part of the problem is, as usual, the stack of programs on top of which FeedWordPress has to sit. In order to get dates from the feed, FeedWordPress has to process two different human-readable date formats. RSS feeds use RFC 822 (or something like it), which FeedWordPress can convert to a Unix timestamp using the PHP strtotime() function. Atom feeds use the W3C DateTime Format, which FeedWordPress can convert to a Unix timestamp using a custom function provided by MagpieRSS, called parse_w3cdtf(). Unix timestamps supposedly do not vary by time zone: a Unix timestamp for a particular time is defined as the number of seconds between that time and 12:00 midnight on January 1, 1970 Greenwich Mean Time. So both strtotime() and parse_w3cdtf() make use of the time zone data provided by the format that they handle, and use it to convert the date-time information to a timestamp based on GMT.

So far so good. Now, when converting a syndicated item into a post for the WordPress database, FeedWordPress has to generate four different timestamps: post_date, which gives the date and time that the post was first published in the local time zone; post_date_gmt, which gives the date and time that the post was first published in Greenwich Mean Time; post_modified, which gives the local date and time that the post was last modified; and post_modified_gmt, which gives the GMT date and time that the post was last modified. These fields are stored in the WordPress database as MySQL DATETIME objects. WordPress later converts them back into Unix timestamps whenever it is necessary for formatting purposes. Here is how I did this in version 0.981:

$post[1] = date('Y-m-d H:i:s',
    (!is_null($post[2][3])
    ? $post[4][5]
    : $post[6][7]));
$post[8] = date('Y-m-d H:i:s',
    $post[9][10]);
$post[11] = gmdate('Y-m-d H:i:s',
    (!is_null($post[12][13])
    ? $post[14][15]
    : $post[16][17]));
$post[18] = gmdate('Y-m-d H:i:s',
    $post[19][20]);

The $post[21] array contains the Unix timestamps that FeedWordPress got from the feed. The PHP date() function formats a Unix timestamp relative to the local time zone. The gmdate() function formats it relative to Greenwich Mean Time. Given that I need local and Greenwich representations of the date and time that the post was first published, and of the date and time that the post was last updated, this seems like the sensible thing to do. But in spite of (or because of) its seeming so sensible, this way of generating post timestamps introduces a subtle bug. More on that later.

When determining whether or not a post has been updated, FeedWordPress uses two items of information: the last-updated date and time provided by the feed, and the last-updated date and time stored in the database. In order to get these two dates into a common format so that they can be compared, FeedWordPress uses either strtotime() or parse_w3cdtf() to convert the feed’s last-updated date and time to a Unix timestamp, and it uses the MySQL function UNIX_TIMESTAMP() to get a Unix timestamp from the last-updated date and time in the database. So here is how I did this in version 0.981:

    $guid = $post[22];
    $result = $wpdb->get_row("
    SELECT id, guid, UNIX_TIMESTAMP(post_modified) AS modified
    FROM $wpdb->posts WHERE guid='$guid'
    ");


if (!$result) : $freshness = 2; // New content elseif ($post[23][24] > $result->modified) : $freshness = 1; // Updated content else : $freshness = 0; endif;

strtotime() and parse_w3cdtf() take account of time zone data; UNIX_TIMESTAMP() presumes that the time is in the local time zone. So, I just need to feed it the DATETIME object that’s in the local time zone — post_modified instead of post_modified_gmt, right? Wrong. Again, in spite of (or because of) its seeming so sensible, this way of comparing timestamps introduces a subtle bug.

Here’s what was wrong with both of these sensible steps: in any given WordPress installation, there are three potentially distinct local time zones that you have to consider:

  1. The local time zone of the web server on which WordPress is running (usually set by the web server’s administrator);

  2. The local time zone of the MySQL server that provides the WordPress database (usually set by the MySQL server’s administrator);

  3. The local time zone set by the user in WordPress’s General Options page (set by the blog owner);

If any of these differ from each other, then the mismatch could cause problems for the way that older versions of FeedWordPress handled dates.

When the PHP date and time functions convert back and forth between human-readable formats and Unix time stamps, they do so relative to the web server’s local time zone. When MySQL converts a DATETIME object to a Unix timestamp, it does so relative to the MySQL server’s local time zone. When WordPress prepares dates and times for storage in the database, or processes them for sorting and displaying posts, it does so relative to WordPress’s local time zone, as set under General Options.

So, when FeedWordPress used the PHP date and time functions to generate post_date and post_modified, it used the wrong time zone — WordPress expects these to be local times in the time zone set under General Options, but the PHP functions use the web server’s local time zone. If the user has set a different time zone from the web server’s default time zone, then this date and time information will be incorrect. In order to get the correct time, we need to get the time zone information from the WordPress database, and then manually apply that offset, rather than leaning on PHP’s date and time functions. So here is how we do this task in FeedWordPress 0.99:

// Dealing with timestamps in WordPress is so fucking fucked.
$offset = (int) get_option('gmt_offset') * 60 * 60;
$this->post[25] =
    gmdate('Y-m-d H:i:s', $this->published() + $offset);
$this->post[26] =
    gmdate('Y-m-d H:i:s', $this->updated() + $offset);
$this->post[27] =
    gmdate('Y-m-d H:i:s', $this->published());
$this->post[28] =
    gmdate('Y-m-d H:i:s', $this->updated());

Note that we use gmdate() in all cases, because we are doing the time zone offset manually rather than letting PHP do it for us.

On the other hand, when FeedWordPress used the MySQL date and time functions to convert MySQL DATETIME objects to Unix timestamps, it used the wrong time zone again — since it was using post_modified, and in the older versions of FeedWordPress post_modified was generated using PHP date and time functions, the time was a local time relative to the web server’s time zone. But UNIX_TIMESTAMP() presumes that the time it is given is a local time relative to the MySQL server’s time zone. Usually this difference should cause no problem, since the web server and the MySQL server are the same machine, or if not the same machine, at least machines that are located in the same place as one another. But if that assumption ever fails, UNIX_TIMESTAMP() will return the wrong time–a time that is either a few hours before, or a few hours after, the real last-updated time. Which will mean that posts either get updated when there’s nothing new to update, or don’t get updated even when there is something new to include.

So we need to convert the MySQL DATETIME value to a Unix timestamp in a context where we know, and can set, the right time zone for the conversion. In this case, the best thing to do is to get the GMT date and time of the last update (in order to avoid issues that might arise from changes in the local timezone setting in WordPress), and then manually convert that into a Unix timestamp using a function that works relative to GMT. So here’s how FeedWordPress 0.99 checks the update times against each other:

$guid = $wpdb->escape($this->guid());


$result = $wpdb->get_row(" SELECT id, guid, post_modified_gmt FROM $wpdb->posts WHERE guid='$guid' ");

preg_match('/([29]+)-([30]+)-([31]+) ([32]+):([33]+):([34]+)/', $result->post_modified_gmt, $backref); $updated = gmmktime($backref[35], $backref[36], $backref[37], $backref[38], $backref[39], $backref[40]); if (!$result) : $this->_freshness = 2; // New content elseif ($this->updated() > $updated) : $this->_freshness = 1; // Updated content $this->_wp_id = $result->id; else : $this->_freshness = 0; // Same old, same old $this->_wp_id = $result->id; endif;

post_modified_gmt always returns the string representation of a MySQL DATETIME object; the regular expression breaks that representation down into its component parts; and the PHP function gmmktime() reassembles those parts into a Unix timestamp. (You might worry that using the PHP date and time functions might reintroduce the first problem, since it doesn’t account for the local time zone set in WordPress. But since everything is guaranteed to be in GMT, this problem doesn’t arise.)

Strictly speaking, it would probably be better to use MySQL functions, instead of a regular expression, to extract the parts of the MySQL DATETIME object, since ostensibly MySQL knows more about its internal formats than PHP does. In practice this is not likely to make a difference, but it’s likely that in future releases of FeedWordPress I’ll change the section to something more like this:

$guid = $wpdb->escape($this->guid());


$result = $wpdb->get_row(" SELECT id, guid, YEAR(post_modified_gmt) AS year, MONTH(post_modified_gmt) AS month, DAYOFMONTH(post_modified_gmt) AS day, HOUR(post_modified_gmt) AS hour, MINUTE(post_modified_gmt) AS minute, SECOND(post_modified_gmt) AS second FROM $wpdb->posts WHERE guid='$guid' ");

if (!$result) : $this->_freshness = 2; // New content else: $updated = gmmktime( $result->hour, $result->minute, $result->second, $result->month, $result->day, $result->year ); if ($this->updated() > $updated) : $this->_freshness = 1; // Updated content $this->_wp_id = $result->id; else : $this->_freshness = 0; // Same old, same old $this->_wp_id = $result->id; endif; endif;

Oh, and in case you were wondering, the reason that moving across the country helped me find this out is that I moved from Eastern Time to Pacific Time, and I changed the default time zone on one of my web servers before I changed the default time zone on my MySQL server. The mismatch exposed this bug while I was doing testing for the most recent release of FeedWordPress. Not particularly interesting, but it did expose a bug to fix, and I hope the guide to time zone issues that resulted may be of some interest.

FeedWordPress 0.99 is hereby released; enjoy WP 2.2 and 2.3 compatibility, bug fixes, major new features, updates without cron

Update 2007-11-21: FeedWordPress 0.99 is now out of date. You can download the latest release — 0.991 at the time of this writing — from the project homepage.

The public (non-beta) release of FeedWordPress version 0.99 is now available for download.

There have been changes to the way that FeedWordPress’s code is organized since version 0.98. If you successfully installed either of the beta releases, you don’t need to do anything special to install the current release. However, if you are upgrading from version 0.98 or before, be sure to see the installation instructions below.

Changes since version 0.98

This release provides compatibility with WordPress 2.2 and 2.3. It has been extensively tested against WordPress version 2.2.3 and the version 2.3 release candidate. I think that all the compatibility issues have been hammered out; of course, if you notice any problems, please let me know and I’ll get on a bugfix as soon as possible.

Version 0.99 also includes an overhaul to the user interface, some significant new features, and a number of bug fixes:

  • AUTOMATIC UPDATES WITHOUT CRON: FeedWordPress now allows you to
    automatically schedule checks for new posts without using external task
    scheduling tools such as cron. In order to enable automatic updates, go
    to Syndication –> Options and set “Check for new posts” to
    “automatically.” When this option is turned on, FeedWordPress will check for new posts
    automatically (1) when someone views your page, (2) if it has been ten minutes (or
    whatever interval you set) since the last time someone viewed your page. This offers a
    way to keep FeedWordPress up-to-date without having to schedule a cron script. It also
    simplifies the process of updating if you do choose to use a cron script — just have curl
    fetch your home page on a fixed schedule (so, for example, I would execute
    curl http://feministblogs.org/ every 15 minutes to keep Feminist Blogs up-to-date).
    Note that this is not the same thing as precisely scheduled updates — at a minimum,
    FeedWordPress will not check for new posts unless and until the next time somebody
    views your page. But for practical purposes it does allow you to keep your aggregator
    updated without having to run cron, and it is as close to precisely scheduled updates as
    you can get without using real scheduling tools such as cron.

    An important side-effect of the changes to the update system is that if
    you were previously using the cron job and the update-feeds.php script
    to schedule updates, you need to change your cron set-up. The old
    update-feeds.php script no longer exists. Instead, if you wish to use
    a cron job to guarantee updates on a particular schedule, you should
    have the cron job fetch the front page of your blog (for example, by
    using curl http://www.zyx.com/blog/ > /dev/null) instead of activating
    the update-feeds.php script. If automatic updates have been enabled,
    fetching the front page will automatically trigger the update process.

  • INTERFACE REORGANIZATION: All FeedWordPress functions are now located
    under a top-level “Syndication” menu in the WordPress Dashboard. To
    manage the list of syndicated sites, manually check for new posts on
    one or more feeds, or syndicate a new site, you should use the main page
    under Syndication. To change global settings for FeedWordPress,
    you should use Syndication –> Options.

  • FILE STRUCTURE REORGANIZATION: Due to a combination of changing styles
    for FeedWordPress plugins and lingering bugs in the FeedWordPress admin
    menu code, the code for FeedWordPress is now contained in two different
    PHP files, which should be installed together in a subdirectory of your
    plugins directory named feedwordpress. (See README.text for
    installation and upgrade instructions relating to the change.)

  • MULTIPLE CATEGORIES SETTING: Some feeds use non-standard methods to
    indicate multiple categories within a single category element. (The most
    popular site to do this is del.icio.us, which separates tags with a
    space.) FeedWordPress now allows you to set an optional setting, for any
    feed which does this, indicating the character or characters used to
    divide multiple categories, using a Perl-compatible regular expression.
    (In the case of del.icio.us feeds, FeedWordPress will automatically use
    \s for the pattern without your having to do any further configuration.)
    To turn this setting on, simply use the “Edit” link for the feed that
    you want to turn it on for.

  • REGULAR EXPRESSION BUG FIXED: Eliminated a minor bug in the regular
    expressions for e-mail addresses (used in parsing RSS author
    elements), which could produce unsightly error messages for some users
    parsing RSS 2.0 feeds.

  • DATE / UPDATE BUG FIXED: A bug in date handling was eliminated that may
    have caused problems if any of (1) WordPress, or (2) PHP, or (3) your
    web server, or (4) your MySQL server, has been set to use a different
    time zone from the one that any of the others is set to use. If
    FeedWordPress has not been properly updating updated posts, or has been
    updating posts when there shouldn’t be any changes for the update, this
    release may solve that problem.

  • GOOGLE READER BUGS FIXED: A couple of bugs that made it difficult for
    FeedWordPress to interact with Google Reader public feeds have been
    fixed. Firstly, if you encountered an error message reading “There was a
    problem adding the newsfeed. ” when you tried to add the feed,
    the cause of this error has been fixed. Secondly, if you succeeded in
    getting FeedWordPress to check a Google Reader feed, only to find that
    the title of posts had junk squashed on to the end of them, that bug
    has been fixed too. To fix this bug, you must install the newest version
    of the optional MagpieRSS upgrade.

  • FILTER PARAMETERS: Due to an old, old bug in WordPress 1.5.0 (which was
    what was available back when I first wrote the filter interface),
    FeedWordPress has traditionally only passed one parameter to
    syndicateditem and syndicatedpost filters functions — an array
    containing either the Magpie representation of a syndicated item from
    the feed, or the database representation of a post about to be inserted
    into the WordPress database. If you needed information about the feed
    that the item came from, this was accessible only through a pair of
    global variables, $fwpchannel and $fwpfeedmeta.

    Since it’s been a pretty long time since WordPress 1.5.0 was in
    widespread usage, I have gone ahead and added an optional second
    parameter to the invocation of the syndicateditem and syndicatedpost
    filters. If you have written a filter for FeedWordPress that uses either
    of these hooks, you can now register that filter to accept 2 parameters.
    If you do so, the second parameter will be a SyndicatedPost object,
    which, among other things, allows you to access information about the
    feed from which an item is syndicated using the $post->feed and the
    $post->feedmeta elements (where $post is the name of the second
    parameter).

    NOTE THAT THE OLD GLOBAL VARIABLES ARE STILL AVAILABLE, for the time
    being at least, so existing filters will not break with the upgrade.
    They should be considered deprecated, however, and may be eliminated in
    the future.

  • FILTER CHANGE / BUGFIX: the array that is passed as the first argument
    syndicatedpost filters no longer is no longer backslash-escaped for
    MySQL when filters are called. This was originally a bug, or an
    oversight; the contents of the array should only be escaped for the
    database after they have gone through all filters. IF YOU HAVE WRITTEN
    ANY syndicated
    post FILTERS THAT PRESUME THE OLD BEHAVIOR OF PASSING IN
    STRINGS THAT ARE ALREADY BACKSLASH-ESCAPED, UPDATE YOUR FILTERS
    ACCORDINGLY.

  • OTHER MINOR BUGFIXES AND INTERNAL CHANGES: The internal architecture of
    FeedWordPress has been significantly changed to make the code more
    modular and clean; hopefully this should help reduce the number of
    compatibility updates that are needed, and make them easier and quicker
    when they are needed.

Installation instructions

To upgrade an existing installation of FeedWordPress to version 0.99:

  1. Download the FeedWordPress archive in zip or gzipped tar format and
    extract the files on your computer.

  2. If you are upgrading from version 0.98 or earlier, then you need to
    create a new directory named feedwordpress in the wp-content/plugins
    directory of your WordPress installation, and you also need to delete
    your existing wp-content/update-feeds.php and
    wp-content/plugins/feedwordpress.php files. The file structure for
    FeedWordPress has changed and the files from your old version will not
    be overwritten, which could cause conflicts if you leave them in place.

  3. Upload the new PHP files to wp-content/plugins/feedwordpress,
    overwriting any existing FeedWordPress files that are there. Also be
    sure to upgrade wp-includes/rss.php and
    wp-includes/rss-functions.php if you use the optional MagpieRSS
    upgrade, or don’t use it yet but do want to syndicate Atom 1.0 feeds.

  4. If you are upgrading from version 0.96 or earlier, immediately log
    in to the WordPress Dashboard, and go to Options –> Syndicated. Follow
    the directions to launch the database upgrade procedure. The new
    versions of FeedWordPress incorporate some long-needed improvements, but
    old meta-data needs to be updated to prevent duplicate posts and other
    possible maladies. If you’re upgrading an existing installation, updates
    and FeedWordPress template functions will not work until you’ve done
    the upgrade. Then take a coffee break while the upgrade runs. It should,
    hopefully, finish within a few minutes even on relatively large
    databases.

  5. If you are upgrading from version 0.98 or earlier, note that the old
    update-feeds.php has been eliminated in favor of a (hopefully) more
    humane method for automatic updating. If you used a cron job for
    scheduled updates, it will not work anymore, but there is another,
    simpler method which will. See Setting Up Feed Updates to get
    scheduled updates back on track.

  6. Enjoy your new installation of FeedWordPress.

FeedWordPress 0.99 beta 2. Testers still wanted.

Update 2007-11-21: FeedWordPress 0.99b2 is now out of date. You can download the latest release — 0.991 at the time of this writing — from the project homepage.

Thanks to everyone who has been trying out the first beta release of FeedWordPress 0.99. In response to my own tests and some feedback from users, I have prepared a second beta release, FeedWordPress 0.99b2, which all are welcome to download and test out.

Again, by beta, I really mean beta, not one of those Web 2.0 perpetual beta releases. The point is to put it out there for you all to test out. You should not count on this release being in full working order. There are features due for this release that I haven’t implemented. I will implement them later. Those that I have implemented all seem to be in working order on my end of things, but everyone’s Apache/PHP/MySQL/WordPress/etc. stack is so different that it’s hard to make any promises about how it will work for you, until y’all have had some time to test it.

With that pre-amble out of the way, if you are interested in testing out the beta release, you should feel free to download FeedWordPress 0.99b2, read the installation instructions below, give it a test run, and let me know how it works for you.

If you are installing this beta as an upgrade over FeedWordPress 0.981 or earlier, please be sure to follow the installation instructions below. If you have already installed 0.99b1 and are installing the new beta over that, you can just replace the old files with the new ones.

Installation instructions: You may have noticed that I have reorganized the file structure of the plugin. Because of this, if you have an older release of FeedWordPress installed, and intend to install the beta release over the older release, first you are going to need to go to your old installation and delete two of the old installation’s files (wp-content/update-feeds.php and wp-content/feedwordpress.php). Once this is done, create a new directory in your plugins directory, wp-content/plugins/feedwordpress/, and copy the new release’s plugin files to this directory. If you don’t delete the old files, then they will not get overwritten and there may be a conflict between the old and new versions. If you do not install the new files in their own directory, the menus will probably not work correctly.

Enjoy, and let me know how it works for you!

FeedWordPress 0.99 beta 1. Testers wanted.

Update 2007-11-21: FeedWordPress 0.99b1 is now out of date. You can download the latest release — 0.991 at the time of this writing — from the project homepage.

It’s been a long time, for several different personal reasons that don’t bear going into here, but the good news is that I am at long last coming back to active development on FeedWordPress. There are some bug fixes and a lot of feature requests that have been waiting for quite some time; and I will be able to start cleaning these out over the course of the next few days.

Toward that end, I am putting out a beta release of the next version of FeedWordPress. By beta, I really mean beta, not one of those Web 2.0 perpetual beta releases. The point is to put it out there for you all to test out. You should not count on this release being in full working order. There are features due for this release that I haven’t implemented. I will implement them later. Those that I have implemented all seem to be in working order on my end of things, but everyone’s Apache/PHP/MySQL/WordPress/etc. stack is so different that it’s hard to make any promises about how it will work for you, until y’all have had some time to test it. With that pre-amble out of the way, if you are interested in testing out the beta release, you should feel free to download FeedWordPress 0.99b1, read the installation instructions below, give it a test run, and let me know how it works for you.

So, what’s new? Well, a fair amount has changed under the hood, much of it things that you won’t notice, but which will hopefully clean up the code a bit and make compatibility releases quicker and easier to put out in the future. But there are a couple of significant changes that you will notice.

  1. First, FeedWordPress functions have been moved to their own top-level menu, currently called Syndication. To syndicate new feeds, change settings on existing feeds, change global options for FeedWordPress, or to manually instruct FeedWordPress to check for new posts, use the Syndication top-level menu and its submenus.

  2. Second, I am changing the way that FeedWordPress checks for new posts. Specifically, I am making the process somewhat less arcane, and making it possible (more or less) to keep up automatic updates even if you do not have access to cron. To turn this feature on, go to Syndication –> Options and tell FeedWordPress to check for new posts automatically. (This option is currently not on by default.)

    When this option is turned on, FeedWordPress will check for new posts automatically (1) when someone views your page, (2) if it has been ten minutes (or whatever interval you set) since the last time someone viewed your page. This offers a way to keep FeedWordPress up-to-date without having to schedule a cron script. It also simplifies the process of updating if you do choose to use a cron script — just have curl fetch your home page on a fixed schedule (so, for example, I would execute curl http://feministblogs.org/ every 15 minutes to keep Feminist Blogs up-to-date). Note that this is not the same thing as precisely scheduled updates — at a minimum, FeedWordPress will not check for new posts unless and until the next time somebody views your page. But for practical purposes it does allow you to keep your aggregator updated without having to run cron, and it is as close to precisely scheduled updates as you can get without using real scheduling tools such as cron.

  3. I have had several questions about compatibility between FeedWordPress and WordPress 2.2. I have checked this beta release with the latest release of WordPress 2.2 on my testbed server and I have encountered no problems. I do not know of any changes between WordPress 2.1 and 2.2 which would introduce an incompatibility. But please let me know if you have any compatibility issues with WordPress 2.2.

    Note that if you are using the beta release of WordPress 2.3, there are changes to the WordPress category system that probably will cause compatibility problems with FeedWordPress. This should hopefully be fixed in the next beta release, which should be out sometime within the next few days.

  4. As mentioned, I have re-organized a bit. Please make sure you follow the installation instructions below.

Installation instructions: You may have noticed that I have reorganized the file structure of the plugin. Because of this, if you have an older release of FeedWordPress installed, and intend to install the beta release over the older release, first you are going to need to go to your old installation and delete two of the old installation’s files (wp-content/update-feeds.php and wp-content/feedwordpress.php). Once this is done, create a new directory in your plugins directory, wp-content/plugins/feedwordpress/, and copy the new release’s plugin files to this directory. If you don’t delete the old files, then they will not get overwritten and there may be a conflict between the old and new versions. If you do not install the new files in their own directory, the menus will probably not work correctly.

Enjoy, and let me know how it works for you!