Custom filter handler in Drupal 7 and Views 3 for data stored as date and datetime

I just needed to expose a table with legacy data for Drupal 7 with Views 3. However, some of the date in the legacy mysql database was stored as DATE and DATETIME. However, Views only supports timestamps for their datehandling, and I needed to use a filter to filter away older posts in the legacy database.

What I needed to do was build a view with “filter >= -10 day”. First I googled and came across this article: Creating a DATETIME View handler for Views. And that article was a good starting point.

I needed to write my own views filter handler. Views 3 in Drupal 7 relies on the autoload feature, so first I needed to declare my handler in the .info-file:

files[] = my_module_handler_filter_datetime.inc

I also needed to state in my_module.module which views api I am using:

core = "7.x"
name = "My Module"
description = "Example on how to add a filter handler with DATETIME"
package = "My modules"
php = "5.2.4"
project = "my_module"
version = "7.x-0.1"
files[] = "my_module_handler_filter_datetime.inc"

<?php
/**
 * Implementation of hook_views_api().
 */
function vih_course_long_legacy_views_api() {
    return array(
        'api' => 3,
        'path' => drupal_get_path('module', 'vih_course_long_legacy'),
    );
}

<?php
/**
 * Implementation of hook_views_data
 */
function my_module_views_data() {
  // Define the base group of this table. Fields that don't
  // have a group defined will go into this field by default.
  $data['my_table']['table']['group'] = t('My table');
  $data['my_table']['table']['base'] = array(
    'field' => 'date',
    'title' => t('My table'),
    'help' => t('My table help description'),
    'database' => 'my_database');
  $data['my_table']['id'] = array(
    'title' => t('Id'),
    'help' => t('Id for the course'),
    'field' => array('handler' => 'views_handler_field'),
    'argument' => array('handler' => 'views_handler_argument_numeric'),
    'filter' => array('handler' => 'views_handler_filter_numeric'),
    'sort' => array('handler' => 'views_handler_sort_numeric'));
  $data['my_table']['date_created'] = array(
    'title' => t('Date created'),
    'help' => t('Date for creation of post'),
    'field' => array('handler' => 'views_handler_field'),
    'argument' => array('handler' => 'views_handler_argument_date'),
    'filter' => array('handler' => 'my_module_handler_filter_datetime'),
    'sort' => array('handler' => 'views_handler_sort_date'));

  return $data;
}

<?php
/**
 * Custom filter handler for views, that handles DATETIME
 */
class my_module_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') {
      // changed from original
      $a = (integer)time() + (integer)sprintf('%+d', $a); // keep sign
      $b = (integer)time() + (integer)sprintf('%+d', $b); // keep sign
      // changed from original ends
    }
    // %s is safe here because strtotime scrubbed the input and we might
    // have a string if using offset.
    $this->query->add_where_expression($this->options['group'], "$field >= '".date("Y-m-d H:i:s", $a)."'");
    $this->query->add_where_expression($this->options['group'], "$field <= '".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_expression($this->options['group'], "$field $this->operator DATE_ADD(NOW(), INTERVAL $value SECOND)");
    } else {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator $value");
    }
  }
}

As you can see, I have a reference to my own filter here:

'filter' => array('handler' => 'my_module_handler_filter_datetime'),

Then I wrote the handler based on the previous mentioned article. There a some smaller changes, but everything should pretty much be self-explanatory. Have a look through the code.

<?php
/**
 * Custom filter handler for views, that handles DATETIME
 */
class my_module_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') {
      // changed from original
      $a = (integer)time() + (integer)sprintf('%+d', $a); // keep sign
      $b = (integer)time() + (integer)sprintf('%+d', $b); // keep sign
      // changed from original ends
    }
    // %s is safe here because strtotime scrubbed the input and we might
    // have a string if using offset.
    $this->query->add_where_expression($this->options['group'], "$field >= '".date("Y-m-d H:i:s", $a)."'");
    $this->query->add_where_expression($this->options['group'], "$field <= '".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_expression($this->options['group'], "$field $this->operator DATE_ADD(NOW(), INTERVAL $value SECOND)");
    } else {
      $this->query->add_where_expression($this->options['group'], "$field $this->operator $value");
    }
  }
}

Caveat

IMHO this should either be built into Views, or it should be easier to achieve e.g. via the date module (and maybe it is). If you have a better solution, do not hesitate to comment.