Creating a DATETIME View handler for Views2
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.