Adding a views date argument creates the following SQL which leads to no results:

SELECT node.created AS node_created, node.nid AS nid
FROM 
{node} node
WHERE (( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(-18000)), '%Y-%m-%d\T%H:%i') >= '2014-08-01T08:45' 
AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(-18000)), '%Y-%m-%d\T%H:%i') <= '2014-08-01T08:45') )AND(( (node.status = '1') )))
ORDER BY node_created DESC
LIMIT 10 OFFSET 0

The SQL above is asking for a time where node.created is both greater than and less than the time argument, which can never be fulfilled. This is from one argument.

There are no options like I seem to recall from 6x as to whether or not this date argument should be interpreted as greater than or less than. Adding a second date argument just creates another impossible condition.

I created a simple view for export which shows this problem.

To replicate, import the view and add the time 2014-08-01T08:45 to the argument preview section, and enable SQL reporting under the views options to see the views-generated SQL.

I know nobody likes it when bugs are marked as "major" but it does seem like this is an important feature for date which is currently broken. If you disagree, please just mark it down to "normal."

$view = new view();
$view->name = 'date_argument_bug';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Date Argument Bug';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Date Argument Bug';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'node';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = FALSE;
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Contextual filter: Date: Date (node) */
$handler->display->display_options['arguments']['date_argument']['id'] = 'date_argument';
$handler->display->display_options['arguments']['date_argument']['table'] = 'node';
$handler->display->display_options['arguments']['date_argument']['field'] = 'date_argument';
$handler->display->display_options['arguments']['date_argument']['default_action'] = 'not found';
$handler->display->display_options['arguments']['date_argument']['default_argument_type'] = 'date';
$handler->display->display_options['arguments']['date_argument']['granularity'] = 'hour';
$handler->display->display_options['arguments']['date_argument']['use_fromto'] = 'no';
$handler->display->display_options['arguments']['date_argument']['date_fields'] = array(
  'node.created' => 'node.created',
);
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'date-argument-bug/%';
$translatables['date_argument_bug'] = array(
  t('Master'),
  t('Date Argument Bug'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('« first'),
  t('‹ previous'),
  t('next ›'),
  t('last »'),
  t('All'),
  t('Page'),
);

Comments

glass.dimly’s picture

glass.dimly CreditAttribution: glass.dimly commented

This code is a workaround, it modifies the query before it's executed to match my expectations of how date arguments should work.

The following code requires that you set up two arguments of Date:whatever field and then set the url to take two date arguments.

The first field will be used as the "date greater than" field and the second will be used as the "date less than."

You have to probably do some investigation to change the argument names and what have you, but basically it will work. Oh, and you have to register your include file with views api.

Hope we can get a real fix, I looked at the code and understanding it then fixing it would have taken longer than I had to fix this unexpected problem.

function module_name_views_query_alter(&$view, &$query) {
  if ($view->name == 'view_name'){
    if (isset($query->where['date']['conditions'][0]['value'][':commerce_line_item_date_argument1']) && isset($query->where['date']['conditions'][1]['value'][':commerce_line_item_date_argument2'])) {
      $query->where['date']['conditions'][0]['value'][':commerce_line_item_date_argument1'] = $query->where['date']['conditions'][1]['value'][':commerce_line_item_date_argument2'];
      unset($query->where['date']['conditions'][1]);
    }
  }
}

Cheers,
glass.dimly