dave.st

Background

A couple of years ago I created a Drupal module to instantiate and process a content type for my own calendar of events. The "proper" Event module seemed too complicated (at least for a newbie as I then was) and anyway I wanted some practice at coding a module.

However I did take a look at Events and this is what prompted me to use DATETIME date columns in my schema declarations, because Event uses that datatype. At the time I wasn't aware that Event is about the only contributed module that does use DATETIME.

Problem

Jump forward nearly two years and I was trying to build a new view with a "filter >= -1 day" against one of my date fields... and it wasn't working.

Solution

I spent quite some time looking at existing Drupal issues and code snippets (particularly #147563 and #293894) but I couldn't find the exact solution that was compatible with D6 / Views 2, so I had to play around until I hit it.

The first thing required was a query substitution placeholder for a date of DATETIME format. This is declared with a hook_views_query_substitutions() function in the main module.

mymodule.module

/**
* Substitute current time; this works with cached queries.
*/
function mymodule_views_query_substitutions($view) {
  global $user;
  $tdata = gmdate('Y-m-d H:i', time());
  return array('***CURRENT_DATETIME***' => $tdata);
}

Then I needed to make sure I declared a custom filter handler in my hook_views_data() function in my .views.inc file. (The code snippet only shows one date field, the approach is the same for the others I had.)

mymodule.views.inc

/**
 * Implementation of hook_views_data()
 */
function mymodule_views_data() {
  // ----------------------------------------------------------------------
  // mymodule table

  $data = array(
    'mymodule' =>array(
      'table' => array(
        'group' => t('Event'),
        'join' => array(
          'node' => array(
            'left_field' => 'nid',
            'field' => 'nid',
          ),
        ),
      ),
      'field_mymodule_date_from' => array(
        'title' => t('Date from'),
        'help' => t('Event start date'),
        'field' => array('handler' => 'views_handler_field_date',),
        'filter' => array('handler' => mymodule_handler_filter_datetime,),
        'sort' => array('handler' => 'views_handler_sort_date',),
      ),
    ),
  );

  return $data;

}

I also needed to declare the handler itself (also in mymodule.views.inc)

/**
 *  Implementation of hook_views_handlers.
 */
function mymodule_views_handlers() {
  return array(
    'info' => array(
      'path' => drupal_get_path('module', 'mymodule'),
    ),
    'handlers' => array(
      'mymodule_handler_filter_datetime' => array(
        'parent' => 'views_handler_filter_date',
      ),
    ),
  );
}

The last component is the handler code itself. This is very similar to standard handlers but is tweaked for the requirements of DATETIME. I think the name of the include file is automatically predicted and included by View.

mymodule_handler_filter_datetime.inc

class mymodule_handler_filter_datetime extends views_handler_filter_date {
  function op_between($field) {
    if ($this->operator == 'between') {
      $a = intval(strtotime($this->value['min'], 0));
      $b = intval(strtotime($this->value['max'], 0));
    }
    else {
      $a = intval(strtotime($this->value['max'], 0));
      $b = intval(strtotime($this->value['min'], 0));
    }

    if ($this->value['type'] == 'offset') {
      $a = (integer)time() + (integer)sprintf('%+d', $a); // keep sign
      $b = (integer)time() + (integer)sprintf('%+d', $b); // keep sign
    }
    // %s is safe here because strtotime scrubbed the input and we might
    // have a string if using offset.
    $this->query->add_where($this->options['group'], "$field >= '%s'", date("Y-m-d H:i:s", $a));
    $this->query->add_where($this->options['group'], "$field <= '%s'", date("Y-m-d H:i:s", $b));
  }

  function op_simple($field) {
    $value = intval(strtotime($this->value['value'], 0));
    if (!empty($this->value['type']) && $this->value['type'] == 'offset') {
      $this->query->add_where($this->options['group'], 
        "%s %s DATE_ADD('***CURRENT_DATETIME***', INTERVAL %d SECOND)",
        $field, $this->operator, $value);
    }
    else {
      $this->query->add_where($this->options['group'], "$field $this->operator %s", $value);
    }
  }
}

Caveat

It's quite possible that to handle the DATETIME data type for all kinds of View activity, I need more custom handlers. However so far the sorting and general field behaviour is OK - it was just the filtering that was obviously not right.