When a retrieving site polls the distribution site for new content, it sends a time code which is parsed and handled by the "node updated since..." views argument provided by the content_distribution module. To avoid timezone issues, the module authors have wisely chosen to compare UTC date/time values. The time code sent by the retrieving site is compared against a time code that is generated by the argument handler based on node.changed.

Now, views does this thing to help date calculations be more predictable: it configures the database timezone offset to be UTC. This means that when you use a function, such as DATE_FORMAT(), to build a timecode using a time value built with FROM_UNIXTIME(), you end up with the UTC time at the moment of that UNIX time integer. That's exactly what we want in this case -- to have node.changed rendered into a timecode in UTC.

Here's the problem:

The "node updated since..." views argument handler included with content_distribution uses views_date_sql_format() to build the formula that ends up in the SQL query. One of the nice features of views_date_sql_format() is that it applies timezone offsets automatically (unless you specify an offset) to get accurate date/time representations for the site's timezone. So if the developer doesn't specify an offset, views effectively assumes the input time is already in UTC, and converts it to the site's timezone for display. This is why, on a site configured for EDT, you would see this in the content_distribution view query:

DATE_FORMAT((FROM_UNIXTIME(node.changed) + INTERVAL -14400 SECOND), '%Y%m%d%H%i')

node.changed gets the Drupal site's tz offset applied to it -- which means the time we're going to compare against the incoming timecode from the retrieving site is back in the Drupal site's configured timezone. The fix is simply a modified call to views_date_sql_format() wherin we tell views not to apply any offsets. This results in an accurate UTC comparison, results in a cleaner query, and is (in theory) actually faster.

Patch forthcoming.

CommentFileSizeAuthor
#2 content_distribution-822064-2.patch1.31 KBJosh Benner
#1 content_distribution-822064.patch1.32 KBJosh Benner

Comments

Josh Benner’s picture

Josh Benner commented
Status: Active » Needs review
StatusFileSize
new content_distribution-822064.patch1.32 KB

Fix described in original post attached as patch.

Josh Benner’s picture

Josh Benner commented
StatusFileSize
new content_distribution-822064-2.patch1.31 KB

New patch makes sure that views does in fact UTC-ize the database by including call to views_get_timezone().

joachim’s picture

joachim commented
Status: Needs review » Needs work 
+++ includes/content_distribution_handler_argument_node_updated_since.inc	9 Jun 2010 16:16:48 -0000
@@ -15,7 +15,15 @@ class content_distribution_handler_argum
+    /**
+     * Views configures date functions to operate in UTC, so we need to pass

Comments within functions behind // please.

+++ includes/content_distribution_handler_argument_node_updated_since.inc	9 Jun 2010 16:16:48 -0000
@@ -15,7 +15,15 @@ class content_distribution_handler_argum
+    views_get_timezone();

What does the void call to this do?

This patch scares me... :/

Powered by Dreditor.

Josh Benner’s picture

Josh Benner commented

views_get_timezone() takes no arguments and returns the timezone offset for the user if configurable timezones are enabled, or else for the site. It also sends a query like this to the database (when using MySQL):

SET @@session.time_zone = '+00:00'

Normally, MySQL will operate with @@session.time_zone == "SYSTEM" (whatever the system timezone is). This setting directly impacts the values output by DATE_FORMAT() and other date/time functions in the database, which can directly affect the values kicked out when views_date_sql_format() is used.

The reason I had to add a call to views_get_timezone() is because I added a 0 as the 4th parameter to views_date_sql_format(), which specifies an offset of 0 -- which means that views_date_sql_format() will not need to determine the timezone it's operating in, which means it will not lead to a call to views_get_timezone(), which means the database will remain operating in the system's timezone.

So, an alternate approach would be to put code in place of the views_get_timezone() that would determine the appropriate offset to use, and pass that to views_date_sql_format() instead (and thus by specifying the offset rather than having views determine it itself, views_get_timezone() will not be called, and no change to the database timezone will be made). The risk to this is if any other view field or filter is in place that ends up triggering views_get_timezone() -- then we could end up back right were we are now. So, safest route is to call views_get_timezone() (which is harmless, and is done now deeper in the call stack anyway) and pass a 0 offset (UTC) to the views date format function.

joachim’s picture

joachim commented

> Normally, MySQL will operate with @@session.time_zone == "SYSTEM" (whatever the system timezone is).

I've got to ask... who on earth puts their system in something other than UTC?

Is this the case this patch caters for?

Josh Benner’s picture

Josh Benner commented

Local dev systems are often non-UTC, as well as many managed servers. While the traditional UNIX wisdom was to always go UTC, the reality is very different in real life.

This issue came to light when I encountered different behavior in the production (UTC) environment versus my local development machine (which is set to my local timezone, naturally). This patch caters to properly handling timezones, which may be set to non-UTC even in production environments.