Date field timezone is ignored in views when used as a filter or argument without minutes granularity

I'm seeing this problem in 6.x-2.x-dev as of Nov 27 2011. The views date filter does not respect the time zone when returning filtered results using the day granularity. Here is what I see as of today's date:

http://www.cctvcambridge.org/schedule

I'm displaying a list of nodes filtered by a lot of criteria, but the exposed default date filter is set to "equal to now" with a day granularity. The result is a list of nodes that returns results from 7PM yesterday to 7PM today, rather than 12AM-12AM. I believe that this is a poor job of the date filter to respect the time zone. Changing the timezone from NY to LA does shift the node results by the corresponding time difference. See the screenshot of the configuration on the filter.

When I move to the official 6.x-2.7 release of Jan 2011, the filtering works as it should, but brings up another major bug for me: http://drupal.org/node/686394 that just needs a patch committed.

I believe this is also the bug I am experiencing. I've attached an exported view which filters an "event" content type with a date field where the date is BETWEEN absolute values of a single day (from 2011-12-01 to 2011-12-01). I've created test content with start and end times which span a portion of the day (morning, afternoon, night) and all nodes are returned except for the one with a time range of 18:00 - 23:59. My Drupal timezone is set to America/Montreal so my offset is -5 so any dates after 7pm will not be included in the result.

Here is the query generated by my previously attached view.

SELECT DISTINCT(node.nid) AS nid, node.title AS node_title

FROM node node
LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid

WHERE
 (node.type in ('event'))
 AND
 (
   (
      DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-12-01'
      AND
      DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-12-01'
   )
   AND
   (
      DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-12-01'
      AND
      DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') <= '2011-12-01'
   )
 )

GROUP BY nid

Because dates are stored in the database in UTC format this query is insufficient. It does not supply the Drupal timezone offset so it will only query against the UTC datetime as far as I can tell. That is the result I'm seeing on my local environment (system timezone = mysql timezone = drupal timezone). Date fields containing "2011-12-01T04:00:00" (November 30, 2011 23:00:00 EST) are included in the query result while date fields containing "2011-12-02T04:00:00" (December 01, 2011 23:00:00 EST) are not.

It seems that the timezone offset needs to be added to the query, especially if the date "granularity" is day or lower. I've added CONVERT_TZ to the query as follows and it resolves this issue for my case.

SELECT DISTINCT(node.nid) AS nid, node.title AS node_title

FROM node node
LEFT JOIN content_field_date node_data_field_date ON node.vid = node_data_field_date.vid

WHERE
 (node.type in ('event'))
 AND
 (
   (
      DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '+00:00', '-05:00'), '%Y-%m-%d') >= '2011-12-01'
      AND
      DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), '+00:00', '-05:00'), '%Y-%m-%d') <= '2011-12-01'
   )
   AND
   (
      DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '+00:00', '-05:00'), '%Y-%m-%d') >= '2011-12-01'
      AND
      DATE_FORMAT(CONVERT_TZ(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), '+00:00', '-05:00'), '%Y-%m-%d') <= '2011-12-01'
   )
 )

GROUP BY nid

The CONVERT_TZ function is added to Mysql since 4.1.3 so should be safe to use. I haven't looked at the date module code to see where this should be changed.

I've narrowed the problem down to pre_query() in date_api_filter_handler.inc. Since the granularity of my view is "day" the offset is discarded. This function needs more intelligence.

  function pre_query() {
    // Setting the offset to avoid trying to use timezone adjustments for dates that don't need it.
    $this->offset = in_array($this->date_handler->granularity, array('hour', 'minute', 'sec')) ? NULL : 0;
  }

I've done further testing and discovered that Mysql configuration is partly to blame. Mysql has no time zone description tables so the check for timezone support in db_tz_support() failed. The following command in an Ubuntu environment (with Drush installed) resolved that.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p
drush variable-delete date_db_tz_support

My view is still not working after this so the code in pre_query() still appears to be a problem. My possibly false assumption is that this code is for performance improvement and not accuracy so here is a patch which removes it. My view now includes CONVERT_TZ in the query with that code removed.

Please comment if that solution is inappropriate. My time to troubleshoot this before the holidays has about run out. I will try to pick this up later in January.

Date field
granularity: minutes
time zone handling: system

OK, the patch itself failed because of malformed lines at #30, but when I manually edited the target files with the patch file as a guide it now shows the correct timezone results - for me. I cannot comment on whether or not this is an appropriate way to fix this issue.

Attaching a screenshot of correct view results to compare to the screenshot in #1.

I've changed the title and status to reflect (what I think is) the actual issue. Please review moderator.

I discovered this issue discussed and supposedly resolved in #1017866 with this commit which added the lines removed by my patch above. Apparently that fix caused this problem.

Here is a re-rolled patch using git.

Bummer, so your fix to another issue created this one. What's the protocol? Do we close this issue and reopen the other?

No, #1017866 is against 7.x-2.x although it was backported while this issue is against 6.x-2.x (at least in my case, speaking of which the version should be changed) so for now I guess this issue stands.

#9: timezone-772082-9.patch queued for re-testing.

Here is a related issue: #1404044: Incorrect granularity value in pre_query() of views argument and filter handlers
Try the fix suggested there....

Also, 6.x-2.x Date tests ARE NOT working right now...

A typo in the array value is not the cause of the timezone issue discussed here. Timezones MUST be considered regardless of the granularity of the filter or argument.

patch #9 solved the issue i was having with a views exposed filter with "day" granularity on ubercart order created / modified date (uc_order).

Changing status to reviewed in hopes of getting some eyes on this by KarenS or another maintainer.

This looks like a duplicate of #1408216: Events no longer appearing in calendar.
I know this issue is older, but that one has seen more activity recently.

There is a similar patch over there too, but that one does not include as many changes as this one does.
Namely, 1408216 does not alter the date_api_filter_handler.inc file as this one does.