Date fields in views broken.

Nobody else is having trouble with date fields in their views?

The field works fine in my content. The date can be edited, saved, created. However, the queries that views generates are not retrieving any nodes.

Here's a query generated by the calendar view with the latest version of Date module (DRUPAL-5--2):

SELECT node.nid, node.title AS node_title, node.changed AS node_changed, CONCAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%%%dT%T'),'|',STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%%%dT%T')) AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_timezone AS node_data_field_date_field_date_timezone, node_data_field_date.field_date_offset AS node_data_field_date_field_date_offset, node_data_field_date.field_date_offset2 AS node_data_field_date_field_date_offset2, node.type FROM {node} node LEFT JOIN {content_type_event} node_data_field_date ON node.vid = node_data_field_date.vid WHERE (%s.%s %s '%s') AND ((STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%%%dT%T') >='2007-12-31 00:00:00' AND STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%%%dT%T') <='2008-01-01 23:59:59'))

Here's the query generated by the same view, same version after I reverted to an earlier version of Date module (from April 7):

SELECT node.nid, node.title AS node_title, node.changed AS node_changed, CONCAT(( REPLACE(node_data_field_date.field_date_value,'T',' ') + INTERVAL (node_data_field_date.field_date_offset) SECOND),'|',( REPLACE(node_data_field_date.field_date_value2,'T',' ') + INTERVAL (node_data_field_date.field_date_offset) SECOND)) AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_timezone AS node_data_field_date_field_date_timezone, node_data_field_date.field_date_offset AS node_data_field_date_field_date_offset, node_data_field_date.field_date_offset2 AS node_data_field_date_field_date_offset2, node.type FROM {node} node LEFT JOIN {content_type_event} node_data_field_date ON node.vid = node_data_field_date.vid WHERE (%s.%s %s '%s') AND ((( REPLACE(node_data_field_date.field_date_value2,'T',' ') + INTERVAL (node_data_field_date.field_date_offset) SECOND) >='2007-12-31 00:00:00' AND ( REPLACE(node_data_field_date.field_date_value,'T',' ') + INTERVAL (node_data_field_date.field_date_offset) SECOND) <='2008-01-01 23:59:59'))

All views on my development site that use date fields are broken. Not just calendar views. Sorry, I'm just now really getting into this problem.. my earlier reports are shallow with facts.

Yup. it's definitely a date bug, because I fixed it! It was quite a puzzler, that's for sure.

There's also a likely fix for a bug which I didn't encounter. It just screamed "typo" of the sort that caused this bug. Perhaps there's some funny escaping going on with a text editor somewhere?

KarenS,

I think there were too many % in the STR_TO_DATE function for MySQL. After passing through the query builder, the output would have been %%d when we need it to be just %d. That made the field always NULL in the query.

The patch definitely fixes the problem I was having. Is it wrong?

KarenS,

The queries are here. Here's the query built by an unpatched Date_api_sql.inc:

SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, CONCAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T'),'|',STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T')) AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_timezone AS node_data_field_date_field_date_timezone, node_data_field_date.field_date_offset AS node_data_field_date_field_date_offset, node_data_field_date.field_date_offset2 AS node_data_field_date_field_date_offset2, node.type FROM node node LEFT JOIN content_type_event node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status = '1') AND ((STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T') >='2008-03-31 00:00:00' AND STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T') <='2008-05-01 23:59:59'))

And here's the query generated with my patch applied:

SELECT DISTINCT(node.nid), node.title AS node_title, node.changed AS node_changed, CONCAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'),'|',STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T')) AS node_data_field_date_field_date_value, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2, node_data_field_date.field_date_timezone AS node_data_field_date_field_date_timezone, node_data_field_date.field_date_offset AS node_data_field_date_field_date_offset, node_data_field_date.field_date_offset2 AS node_data_field_date_field_date_offset2, node.type FROM node node LEFT JOIN content_type_event node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.status = '1') AND ((STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T') >='2008-03-31 00:00:00' AND STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T') <='2008-05-01 23:59:59'))

The key difference is in STR_TO_DATE. The unpatched date makes a query that has an extra percent sign, which is coming from the quadruple escaping of the 'd' placeholder.

KarenS.

All of my views with date fields are affected. If I bring Calendar into the situation, it may just confuse things. Here is a view that I'm using to display valid items of a particular type. Validity is determined by comparing a date field to now(). I apologize if I'm giving you too much information, but I am not sure how to find out which handler I am using.

  $view = new stdClass();
  $view->name = 'action_alerts';
  $view->description = 'Valid action alerts';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'Action Alerts';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'node';
  $view->url = 'issue/action-alerts';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->menu = TRUE;
  $view->menu_title = 'Action Alerts';
  $view->menu_tab = FALSE;
  $view->menu_tab_weight = '0';
  $view->menu_tab_default = FALSE;
  $view->menu_tab_default_parent = NULL;
  $view->menu_tab_default_parent_type = 'tab';
  $view->menu_parent_tab_weight = '0';
  $view->menu_parent_title = '';
  $view->block = TRUE;
  $view->block_title = 'Action Alerts';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'teaser';
  $view->nodes_per_block = '5';
  $view->block_more = TRUE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'sticky',
      'sortorder' => 'DESC',
      'options' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'created',
      'sortorder' => 'DESC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'action_alert',
),
    ),
    array (
      'tablename' => 'node_data_field_valid',
      'field' => 'field_valid_value_default',
      'operator' => '<=',
      'options' => 'now',
      'value' => '',
    ),
    array (
      'tablename' => 'node_data_field_valid',
      'field' => 'field_valid_value_to|default',
      'operator' => '>=',
      'options' => 'now',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node, node_data_field_valid);
  $views[$view->name] = $view;

With my patch, the query produced (for the page view) is:

SELECT DISTINCT(node.nid), node.sticky AS node_sticky, node.created AS node_created_created FROM node node LEFT JOIN content_type_action_alert node_data_field_valid ON node.vid = node_data_field_valid.vid WHERE (node.status = '1') AND (node.type IN ('action_alert')) AND (STR_TO_DATE(node_data_field_valid.field_valid_value, '%Y-%m-%dT%T') <= NOW()) AND (STR_TO_DATE(node_data_field_valid.field_valid_value2, '%Y-%m-%dT%T') >= NOW()) ORDER BY node_sticky DESC, node_created_created DESC LIMIT 0, 10

However the latest checkout makes this query:

SELECT DISTINCT(node.nid), node.sticky AS node_sticky, node.created AS node_created_created FROM node node LEFT JOIN content_type_action_alert node_data_field_valid ON node.vid = node_data_field_valid.vid WHERE (node.status = '1') AND (node.type IN ('action_alert')) AND (STR_TO_DATE(node_data_field_valid.field_valid_value, '%Y-%m-%%dT%T') <= NOW()) AND (STR_TO_DATE(node_data_field_valid.field_valid_value2, '%Y-%m-%%dT%T') >= NOW()) ORDER BY node_sticky DESC, node_created_created DESC LIMIT 0, 10

%Y-%m-%%dT%T for a date format looks wrong to me. It has an extra %.

I'm using PHP 5.2, MySQL 5... standard Debian stable packages.

Ok. Please let me know what I can do to continue to diagnose the problem.

Automatically closed -- issue fixed for two weeks with no activity.