Handling date ranges in Views filter with the 'between' operator

I'm looking for a solution like this, but this patch can't be applied to 6.x-2.x-dev. I also can't find the code in the 6.x-2.0 final version.

Is there any new information about this?

Not sure what the "proper" or more efficient way is, but I prefer to not use the "is between" operator and instead use two different "less than" and "greater than" filters.

Can you tell me more how you did this?

Upon reading the original post again, I realize this is a little more complicated than I originally thought, but still doable, I think.
Here is how the filter could be setup:

Say your date range is 3/15/2009 to 6/15/2009

(From Date "is greater than" 3/15/2009 AND From date "is less than" 6/15/2009)
OR
(To Date "is Greater than" 3/15/2009 AND To Date "is less than" 6/15/2009)
OR
(From Date "is less than" 3/15/2009 AND To Date "is greater than" 6/15/2009)

The problem with exposing this is there would be 6 different values to input.
So it probably is not applicable to your situation/request.

A date views-filter-operater with regard to date-range-fields is *really* necessary for the date api's/field's "to-date" feature to make sense!

How about integrating a filter operator for date-fields with ranges/from-to-span?
The user would have to enter only one date, and this filter operator would match every date field which the entered date falls into, i.e. which has either a (start) date equal to or a start date greater-or-equal and an end date smaller-or-equal to the entered value.

Or is there any sensible way to do that with argument handling code in Views 2?
(Perhaps a generic approach usable also for twi independent "from"/"to" integer number fields? ;)

Would be okay for me as a quick-fix.

cheers,

daniel

Same problem here.

Subscribing.

The patch in #7 works as far I can see. Very nice, and a needed feature.

Thanks!

Hi.
Is there any date_api_filter_handler.overlap.patch for Date 2.3?

Best regards/
dajjen

I think you get the same range with a simpler combination, no? (new to this, so please correct me if I'm wrong)

From date is less than 6/15/2009
AND
To Date is greater than 3/15/2009

Event 1: 4/15 to 7/15 (check)
Event 2: 2/15 to 5/15 (check)
Event 3: 1/15 to 12/15 (check)
Event 4: 4/20 to 4/25 (check)

Event 5: 7/15 to 9/15 (violates first condition)
Event 6: 2/15 to 3/12 (violates second condition)

I have the same issue, though I also have blank dates to wrangle (ongoing event). In VIEWS 2, performing the same filter comparison on multiple dates provides you the choice of an operator (AND or OR), but it seems that separate date filters (when you want a different comparison for your different date fields) are glued with OR (never a choice) as a separate clause in the query, and then appended to the rest of the query (node-type filters etc.) with AND.

Will this patch provide me with new options in VIEWS?
Apologies if i'm missing something here.
Cheers!

subscribing

I am really trying to find out the most viewed content in the last 72 hours. Have you found a way to accomplish this?

sorry my dumbness, but - will it help me in this task:

i have an event from 25 oct 2010 to 30 oct 2010. I want Date work this way:
if i choose From date 20 oct 2010 and To date 27 oct 2010 - it shows this event as matching this range
if i choose From date 27 oct 2010 and To date 03 nov 2010 - it shows this event as matching this range
if i choose From date 01 oct 2010 and To date 01 dec 2010 - it shows this event as matching this range

will this patch do so? Or how else can I solve it?
Thanks in advance :)

The patch definitely looks like it needs a reroll.

Also, I don't agree with adding an 'Overlaps' operator. 'Between' already covers it.

If I say to the Views filter, 'Give me all events between 1 July and 15 July', then I expect 'Family holiday: 10 July - 10 August' to show up in the list of results - part of it happens between those dates, and if my calendar didn't tell me I had that event taking place during the interval I requested, I'd think it was a broken calendar. A patch should fix the behaviour of the 'between' operator, which is not working as it should.

Here's a patch to fix the 'between' operator.

The 'to' field needs to be added to the query, and this is the cleanest way I could see to do it. Would need testing to check other cases aren't broken. Note also I've not had time to handle the 'not between' case, which I imagine needs similar treatment.

Duh, helps if I attach the file...

joachim, thank you soo much :) it really works like i hoped it should do!

Thanks for the review, webroid :)

It occurred to me after posting this patch that a more elegant way to do it might be to add a fake field to the list you select from in the date filter options, so with a CCK date field present, you'd see:

- my_date: From date
- my_date: To date
- my_date: interval

Selecting that and the 'between' operator would consider spanning dates like this patch does. It would have the advantage of not hijacking the regular 'to' date (because you might want to really filter on the actual start date of an event being this week, say!). On the other hand, the field would only be meaningful with the 'between' operator, so you'd need validation to check for that.

well, frankly, i'm not so strong in english+drupal to uderstand fully what do you mean, but thanks anyway, maybe later i will grow and get in to a meaning :) what's more - the patch is working and i don't want to touch anything while it works properly - if you know what i mean :)

joachim, thank you for that awesome patch! It works great. I truly believe that your patch should be integrated upstream.

subscribe. what about "Is not between" operator, so for outside the date range

I tried the patch, but couldn't see how to use it in the UI. I guess I'm dense.

Instead I rammed a pencil into my brain and pulled out a views_query_alter.
This won't be much use outside of the view I'm using it in (I think) but for reference, I'll paste my code here.

/**
 * Rewrite the view that is supposed to show events across a time range to ensure
 * it includes events with a begin or end date outside of the range, but which partially fall within it.
 * Long term events, starting in Sept, ending in Nov, should still be listed when someone asks
 * what is happening between Oct 12th - 15th?
 *
 * I tried for several hours to find solutions in d.o issues, but this is what I have to do.
 * Like any views_query_alter, this is likely to implode if anyone messes with the view ever again.
 * dman 2011-09-07
 */
function sparks_misc_views_query_alter(&$view, &$query) {
  if ($view->name != 'events') {return;}
  #dpm($query);
  // Pick apart the clauses to find the from and to dates defining the search range.
  // These were not given to us as args. Boo.
  // note that either date is optional, so they may not both be there every time.
  $start_range = $end_range = 0;
  foreach ($query->where['date']['clauses'] as $clause) {
    if (preg_match('/ > /', $clause) && preg_match('/\d\d\d\d\-\d\d\-\d\d/', $clause, $matches) ) {
      $start_range = $matches[0];
    }
    if (preg_match('/ <= /', $clause) && preg_match('/\d\d\d\d\-\d\d\-\d\d/', $clause, $matches) ) {
      $end_range = $matches[0];
    }
  }
  // Start re-inventing the clauses.
  $clauses = array();

  // note that .field_date_value and .field_date_value2 are the nodes from and two dates.

  if (!empty($start_range) && !empty($end_range)) {
    // Either the begin date is between the defined range
    $clauses[] = "(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T'), '%Y-%m-%%d') >= '$start_range' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T'), '%Y-%m-%%d') <= '$end_range')";
    // Or the end date is between the range
    $clauses[] = "(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T'), '%Y-%m-%%d') >= '$start_range' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T'), '%Y-%m-%%d') <= '$end_range')";
    // or they straddle it
     $clauses[] = "(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T'), '%Y-%m-%%d') < '$start_range' AND DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T'), '%Y-%m-%%d') > '$end_range')";
  }
  else {
    // not both ends or the range were defined. Deal with that.
    if ($start_range) {
      // Only the start is defined, so list everythin that ends after then (this will include anything that starts)
      $clauses[] = "(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%%dT%T'), '%Y-%m-%%d') >= '$start_range' )";
    }
    if ($end_range) {
      // Only the end is defined, so show anything that begins before then;
       $clauses[] = "(DATE_FORMAT(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%%dT%T'), '%Y-%m-%%d') >= '$end_range' )";
    }
    // if neither range was defined, then there are no clauses and we make no changes.
  }
  if (! empty($clauses)) {
    $query->where['date']['clauses'] = $clauses;
    $query->where['date']['type'] = 'OR';
  } 
  #dpm(get_defined_vars());
}

To achieve the "not between", use the same code in the case 'not between': as joachim posted above for the case 'between':, but alter the first set of sql_parts[] to be this:

$sql_parts[] = $this->date_filter('min', $query_field_to, '<');
$sql_parts[] = $this->date_filter('max', $query_field, '>');

and the set after the else { to be this:

$sql_parts[] = $this->date_filter('min', $query_field, '<');
$sql_parts[] = $this->date_filter('max', $query_field, '>');

Unfortunately the D6 version of this module is no longer supported, but we appreciate the time you put into this. If this problem is relevant for D7 too, please reopen the issue. Thanks.