Better date filter

Did a little more work on this. It now saves and displays the values correctly. Everything looks and works as I want in the Views UI (screenshot1) but when using it as an exposed filter the display is all wrong and the ajax doesn't work to swap out the unneeded values -- I get all three date arrays, 'value', 'min', and 'max' (screenshot2).

Attached is the latest patch.

I played around with another way of doing this using drop-down selectors because they make it easier to see what your options are. Again, it looks just right in the Views UI, but not in the exposed view, which doesn't even pick up the selectors and turns everything back into textfields.

There's *no* date calculation in this patch, this is just creating a way to select a variety of filter values and I'm trying to get the selector to work the same in the exposed filter as it does in the UI. The way this behaves in the UI is exactly what I want it to do.

The diff is hard to read, so to make it easier, I'm changing views_handler_filter_date to this:

class views_handler_filter_date extends views_handler_filter_numeric {
  function date_parts($limit = array()) {
    $parts =  array(
      'year' => t('Year'), 'month' => t('Month'), 'day' => t('Day'),
      'hour' => t('Hour'), 'minute' => t('Minute'), 'second' => t('Second'),
      'offset' => t('Offset'),
      );
    if (!empty($limit)) {
      foreach ($parts as $key => $part) {
        if (!in_array($key, $limit)) {
          unset($parts[$key]);
        }
      }
    }
    return $parts;
  }

  function date_factory($source, $which, $value, $operator_value) {
    $parts = array('year', 'month', 'day', 'hour', 'minute', 'offset');
    foreach ($this->date_parts($parts) as $key => $name) {
      $max = 1;
      switch ($key) {
        case 'month':
          $max = 12;
          break;
        case 'day':
          $max = 31;
          break;
        case 'hour':
          $max = 23;
          break;
        case 'minute':
          $max = 59;
          break;
        case 'second':
          $max = 59;
          break;
      }
      $options = array('' => '', 'now' => 'now');
      $options += drupal_map_assoc(range(1, $max));
      $form[$key] = array(
        '#title' => $name,
        '#type' => $key == 'year' || $key == 'offset' ? 'textfield' : 'select',
        '#size' => $key == 'offset' ? 20 : ($key == 'year' ? 6 : 1),
        '#default_value' => isset($value[$key]) ? $value[$key] : '',
        '#prefix' => '<div style="float:left;margin-right:-20px;padding:0">',
        '#suffix' => '</div>',
      );
      if ($key != 'offset' && $key != 'year') {
        $form[$key]['#options'] = $options;
      }
      if ($which == 'all') {
        $dependency = array(
          '#process' => array('views_process_dependency'),
          '#dependency' => array($source => $this->operator_values($operator_value)),
        );
        $form[$key] += $dependency;
      }
    }
    $form['#prefix'] = '<div class="views-left-75">';
    $form['#suffix'] = '</div>';
    return $form;
  }

  function admin_summary() {
    $labels = $this->date_parts();
    $output = check_plain($this->operator) . ' ';
    if (in_array($this->operator, $this->operator_values(2))) {
      $min = array();
      $max = array();
      foreach ($this->value['min'] as $key => $value) {
        if (!empty($value)) {
          $min[] = $labels[$key] .'='. $value;
        }
      }
      foreach ($this->value['max'] as $key => $value) {
        if (!empty($value)) {
          $max[] = $labels[$key] .'='. $value;
        }
      }
      $min = implode(', ', $min);
      $max = implode(', ', $max);
      $output .= t('@min and @max', array('@min' => $min, '@max' => $max));
    }
    else {
      $values = array();
      foreach ($this->value['value'] as $key => $value) {
        if (!empty($value)) {
          $values[] = $labels[$key] .'='. $value;
        }
      }
      $values = implode(', ', $values);
      $output .= check_plain($values);
    }
    return $output;
  }

  /**
   * Add a type selector to the value form
   */
  function value_form(&$form, &$form_state) {
    parent::value_form($form, $form_state);

    $form['value']['#tree'] = TRUE;

    // We have to make some choices when creating this as an exposed
    // filter form. For example, if the operator is locked and thus
    // not rendered, we can't render dependencies; instead we only
    // render the form items we need.
    $which = 'all';
    if (!empty($form['operator'])) {
      $source = ($form['operator']['#type'] == 'radios') ? 'radio:options[operator]' : 'edit-options-operator';
    }

    if (!empty($form_state['exposed'])) {
      if (empty($this->options['expose']['operator'])) {
        // exposed and locked.
        $which = in_array($this->operator, $this->operator_values(2)) ? 'minmax' : 'value';
      }
      else {
        $source = 'edit-' . form_clean_id($this->options['expose']['operator']);
      }
    }
    if ($which == 'all' || in_array($which, array_keys($this->date_parts()))) {
      $form['value']['value'] = $this->date_factory($source, $which, $this->value['value'], 1);
    }
    $form['value']['min'] = $this->date_factory($source, $which, $this->value['min'], 2);
    $form['value']['max'] = $this->date_factory($source, $which, $this->value['max'], 2);
    $form['value']['description'] = array(
      '#prefix' => '<div class="views-left-75"><div class="form-item"><div class="description">',
      '#suffix' => '</div></div></div>',
      '#value' => t('Blank values do no filtering, \'now\' filters for the current value, \'Offset\' adds a adjustment like \'+1 day\' to the input value. For instance, setting the Year to 2008 and the month to 4 and leaving everything else blank will filter for all dates in April 2008; setting the month to \'now\' and leaving everything else blank will filter for the current month in all years.'));
  }

  function options_validate(&$form, &$form_state) {
    //parent::options_validate($form, $form_state);
    // TODO
  }

  function op_between($field) {
    // TODO
  }

  function op_simple($field) {
    // TODO
  }
}

Just an update in case you're getting close to making a new release and are interested in including this, I figured out my problem with the exposed widget and nearly have this working now.

I have created a flexible, customizable date filter where you can decide what date parts you want to filter on (and expose as options) so you can filter by year and month, a complete date, month only, or any other combination; set either a value or a from/to range; add in an adjustment like "+1 day" in addition to whatever else has been selected; and intelligently handle dates in datetime or iso format as well as timestamps (for that I'm probably going to require modules that want to use this to declare a date type in the filter array).

Once I have this working, I want to do the same with the date arguments to create a single, configurable date argument that works in the same way.

Here's a patch that does the following:

1) Pulls the previous cross-database date SQL into a new date handling class and adds to it other functions that are used for creating date filters and arguments.

2) Replaces the existing date filter with a new flexible filter that has a settings button so you can decide exactly what granularity you want to filter on, with drop-down selectors for all the selected date parts.

3) Adds a new flexible date argument that allows you to select the granularity of the argument so you can use it to do YYYY or YYYY-MM or whatever. This eliminates the need for special one-off arguments for each possible date/field combination and makes it easy to add arguments for other dates.

4) Adds information to the admin panel to make it easy to see how date filters and arguments are configured and what granularity is being used.

5) Fixes a bug in the current code where the argument groupby is grouping by the entire date even if a partial date is used for the filter (which can create duplicate rows for the same day) by overriding the summary handling to create the correct groupby for the granularity of the date.

This should work for any date type. Timestamps are the default, but adding a 'date_type' value of 'datetime' or 'iso' to the $data array will provide enough information to make the date filter and argument work for those types, too.

The 'Week' option is not yet implemented, but any other combination should work correctly.

This is a big patch, but I added the new code at the bottom instead of in the middle of other things to make it easier to read.

If you decide you're not interested in this, I'll add it to the Date module instead, but I think it would be very handy for all date fields.

The widget should allow you to do anything -- within the last 2 months would be setting everything to 'now' and the 'to' offset to '+ 2 months'.

However I agree that making a widget that is ultimately flexible is great for doing complex things but makes it harder to do simple things. I'll try to re-work this into a simpler widget for the usual cases and leave the complex ones to some sort of add-on module.

I probably won't get this done right away, but I'll try to get to it as soon as I can.

I've got a simplified date filter and argument working pretty well and will post a patch shortly.

However in testing it I've run into an oddity of the new ajax forms handling that I need help with. We use views_get_timezone() to get the timezone adjustment for our queries, and that function sets the MYSQL database temporarily to use UTC. This works fine in the actual view, but in the preview my results are off by the amount of the timezone adjustment, so I think that isn't getting applied to the ajax query. I use the NOW() function in places and in the preview it's giving me localtime but in the actual view it's giving me UTC time. I'm not sure what to do to get the same results in the preview as I get in the actual view.

Also on that subject, what are we doing about setting the db timezone for postgres? If it isn't set to UTC, too, we're going to get inconsistent results between MYSQL and POSTGRESQL.

Did some digging around and it looks like we want to use "SET TIME ZONE '+00:00'" in postgres to have the same effect as "SET @@session.time_zone = '+00:00'" in MYSQL, so I'll include that change in my patch.

So I think the remaining hurdle is figuring out how to set that in the ajax session.

Well views_get_timezone() is getting called in the handler, so I guess that is odd since the handler should be processed by both sessions. Maybe it's the static variable that is keeping it from getting into both sessions. I'll play around and see what I can come up with.

I found a fix. It turns out I had the problem backwards -- the preview is getting the timezone right and the regular query was getting it wrong. I added 'SELECT @@session.time_zone' to my query and echoed the results back to be sure what timezone was set when the query was running in both situations.

Anyway I found a simple fix that works -- I add views_get_timezone() right at the beginning of the execute() function of the view class, just before the query gets executed. My code was calling views_get_timezone() while constructing the filter, and maybe something about the filter is cached even if the view itself is not??

Anyway, this is a simple fix that should always work.

I'll include that change in my patch.