When using the Node: Post date filter, any value entered on the offset form field is taken directly as the value for the corresponding SQL query. It should be added to or subtracted from the CURRENT TIME value, but it's not. For instance, when "-60 days" is entered, the SQL query contains 'WHERE node.created >= -5184000', which is obviously the value that should be subtracted from the "CURRENT TIME" value. This currently causes no filtering in most cases, and incorrect filtering otherwise.

This issue doesn't exist in 6.x-3.0-alpha1, but did exist in the previous version of 6.x-3.x-dev.

view export:

$view = new view;
$view->name = 'recent_blog_posts';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Node: Type */
$handler->display->display_options['fields']['type']['id'] = 'type';
$handler->display->display_options['fields']['type']['table'] = 'node';
$handler->display->display_options['fields']['type']['field'] = 'type';
$handler->display->display_options['fields']['type']['label'] = 'blog entry';
$handler->display->display_options['fields']['type']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['type']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['type']['alter']['trim'] = 0;
$handler->display->display_options['fields']['type']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['type']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['type']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['type']['alter']['html'] = 0;
$handler->display->display_options['fields']['type']['hide_empty'] = 0;
$handler->display->display_options['fields']['type']['empty_zero'] = 0;
$handler->display->display_options['fields']['type']['link_to_node'] = 1;
/* Field: Node: Post date */
$handler->display->display_options['fields']['created']['id'] = 'created';
$handler->display->display_options['fields']['created']['table'] = 'node';
$handler->display->display_options['fields']['created']['field'] = 'created';
$handler->display->display_options['fields']['created']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['created']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['created']['alter']['trim'] = 0;
$handler->display->display_options['fields']['created']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['created']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['created']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['created']['alter']['html'] = 0;
$handler->display->display_options['fields']['created']['hide_empty'] = 0;
$handler->display->display_options['fields']['created']['empty_zero'] = 0;
/* Sort criterion: Node: 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';
$handler->display->display_options['sorts']['created']['granularity'] = 'minute';
/* Filter: Node: Post date */
$handler->display->display_options['filters']['created']['id'] = 'created';
$handler->display->display_options['filters']['created']['table'] = 'node';
$handler->display->display_options['filters']['created']['field'] = 'created';
$handler->display->display_options['filters']['created']['operator'] = '>=';
$handler->display->display_options['filters']['created']['value']['value'] = '- 60 days';

Incorrect SQL query is below:

SELECT node.type AS node_type,
node.nid AS nid,
node.created AS node_created,
DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL -21600 SECOND), '%Y%m%d%H%i') AS node_created_minute
FROM node node
WHERE node.created >= -5184000
ORDER BY node_created_minute DESC

CommentFileSizeAuthor
#2 views_654738-3.patch565 bytesScott Reynolds

Comments

Scott Reynolds’s picture

Scott Reynolds commented

Im confirming this. I manually altered my exported view to set the type like so

$handler->display->display_options['filters']['created']['value']['type'] = 'offset';

It appears that the 'type' isn't being saved.

Scott Reynolds’s picture

Scott Reynolds commented
StatusFileSize
new views_654738-3.patch565 bytes

and the patch

dagmar’s picture

dagmar
Primary language Spanish
Location Argentina
commented
Status: Active » Needs review

Yes, looks good. I'm just changing the status.

dawehner’s picture

dawehner
Primary language German
commented
Status: Needs review » Reviewed & tested by the community

i could reproduce the bug.

Patch worked fine, when i manually tested it

merlinofchaos’s picture

merlinofchaos commented
Status: Reviewed & tested by the community » Fixed

Committed to 3.x for both D6 and D7. I'm not sure -- should this be committed to 2.x as well?

Scott Reynolds’s picture

Scott Reynolds commented
Version: 6.x-3.x-dev » 6.x-2.x-dev
Status: Fixed » Needs review

I believe so.

dawehner’s picture

dawehner
Primary language German
commented

i coultn'd reproduce the error on 2.x

Scott Reynolds’s picture

Scott Reynolds commented

Sure, but option_definition() hasn't changed its api right? Just because it works doesn't mean its correct. And shouldn't Views handlers be strong examples of how to implement handlers?

dawehner’s picture

dawehner
Primary language German
commented

hehe :)

The think is, that since 3.x only items in option_definition are saved / can be exported. Before the fapi values got stored.

Scott Reynolds’s picture

Scott Reynolds commented

Right I understand. But this statement is on point

And shouldn't Views handlers be strong examples of how to implement handlers?

merlinofchaos’s picture

merlinofchaos commented

How about this. Confirm for me that it doesn't *break* 2.x and I'll go ahead and commit it.

drupalina’s picture

drupalina commented

I've implemented this line on 12 of views_handler_filter_date.inc of Views 2.10 and now it works perfectly.

Even though to display the last nodes in the last 168 hours I have to enter "Greater of equal to" and "-168 hours", which is a little bit odd as far as the (human) logic is concerned.

merlinofchaos’s picture

merlinofchaos commented

That's because you should be reading it as ">= now - 168 hours" since the 'offset' is offset from now.

drupalina’s picture

drupalina commented

regarding #12 -- I might have rushed with my decision that the patch works perfectly. I had my watchdog fill with loads of PHP errors about "serialize" (even though it could be another module that was causing it). I reverted to the original Views 2.10 handler and I think the created Views are still functioning well as they are. So I'd say that this still needs a bit more testing.

gozigzag’s picture

gozigzag commented
Issue tags: +offset, +date filter

Any status update on whether the date filter is working properly with above patch? What about the patch from http://drupal.org/node/617084? And the suggestion on that page of changing CURRENT_TIME to CURRENT_DATE?

And a question regarding offset (sorry if this is a bit off issue, but not sure if it is related). What about using things like "minus one Saturday" (-1 Saturday)? Does this work with Views date filter? I found this working strangely.

For instance, today is Saturday(local time, site time). If I enter -1 Saturday, it appears to subtract exactly 5 days. If I enter: -1 Sunday, it subtracts 4 days. If I enter -1 Monday, it subtracts 3 days. Clearly, it doesn't work properly. But the fact that it works in some way, makes me wonder what exactly is going on. I want to setup a filter for weekly periods such as every Monday through Sunday.

Anyway, it would be nice just to know if its working properly for things like: -1 week. Has anyone got a well tested working patch for this yet?

Thanks for all the hard work put into this incredible module.

I'm using 6.x-2.11.

gozigzag’s picture

gozigzag commented

I am not sure if this is right -but in my situation I want to filter date against the node created date. I noticed node.created is saved as type integer. So, I think to filter properly it is best to convert CURRENT_TIME into a UNIX_TIMESTAMP.
in views_handler_filter_date.inc around line 146, I adjusted as such

 function op_simple($field) {
    $timestamp = strtotime('now');
	$prevalue = strtotime($this->value['value']);
    $value = $prevalue - $timestamp;
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      $value = 'UNIX_TIMESTAMP(CURRENT_TIMESTAMP) +  '. sprintf('%+d', $value); // keep sign
    }
    $this->query->add_where($this->options['group'], "$field $this->operator %s", $value);
  }
}
merlinofchaos’s picture

merlinofchaos commented
Status: Needs review » Closed (fixed)

Ok, at this point 2.x appears to already work without the patch. I want to start pushing to migrating to 3.x, so this no longer qualifies as a patch that should be committed to 2.x. Closing issue.