Finally I am back blogging after half an year. Its been some good time since my last blog entry, but here I am and let’s get down to business without much fanfare 🙂 So this week came along an interesting problem. The Centre for Transition site we had been working on went live recently and it was all good. However the client came back with a change request regarding the Calendar page. I won’t go into technical details, but suffice to say that the page is generated by Views 3 with data being fetched from nodes and fields attached to nodes, a pretty standard way of doing things in Drupal. However we were also storing some information (schedule) about each node in a custom table. This was necessitated by one to many relationship between each node and schedules. A node basically represented a WorkShop which could have multiple schedules (where each schedule compromised of start/end dates and location among other fields). The change request revolved around exposing start/end dates from each schedule of the WorkShop node on the Calendar page. As the Calendar page was created using Views, this meant exposing our custom table and its columns to Views. It should have been an easy task with the extensibility provided by Views, but it took more time than I anticipated due to Views refusing to recognize Date/Time columns from the custom table. If you have ever developed with Views before, you would know its pretty straight-forward to expose your tables and its columns to Views using hook_views_data. Because our custom table used node as a referenced entity, our table was an extension of node (and not a base table for Views). So I quickly mustered up the following definition for hook_views_data in our custom module:   {syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }function workshop_views_data() { $data = array(); $data[‘workshop_location’][‘table’][‘group’] = t(‘Workshop’); $data[‘workshop_location’][‘table’][‘join’] = array( // Directly links to node table. ‘node’ => array( ‘left_field’ => ‘nid’, ‘field’ => ‘nid’, ), ); //Definition for other fields here. //For the purpose of blog post, I am only enumerating the date/time fields. $data[‘workshop_location’][‘start_date’] = array( ‘title’ => t(‘Location Start Date’), ‘help’ => t(‘Location Start Date’), ‘field’ => array( ‘handler’ => ‘views_handler_field_date’, ‘click sortable’ => TRUE, ), ‘filter’ => array( ‘handler’ => ‘views_handler_filter_date’, ), ‘argument’ => array( ‘handler’ => ‘views_handler_argument_date’, ’empty field name’ => t(‘Undated’), ), ‘sort’ => array( ‘handler’ => ‘views_handler_sort_date’, ) ); $data[‘workshop_location’][‘end_date’] = array( ‘title’ => t(‘Location End Date’), ‘help’ => t(‘Location End Date’), ‘field’ => array( ‘handler’ => ‘views_handler_field_date’, ‘click sortable’ => TRUE, ), ‘filter’ => array( ‘handler’ => ‘views_handler_filter_date’, ), ‘argument’ => array( ‘handler’ => ‘views_handler_argument_date’, ’empty field name’ => t(‘Undated’), ), ‘sort’ => array( ‘handler’ => ‘views_handler_sort_date’, ) ); //More fields here. return $data; }{/syntaxhighlighter} But after clearing Drupal caches, changing View definition to incorporate fields from my custom table and refreshing the Calendar page, I was greeted by tons of these warnings: Warning: date_timezone_set() expects parameter 1 to be DateTime, boolean given in format_date() Surprised, I started looking at the source code for Drupal’s date module to figure out what was going wrong (a point to note is all handlers I have specified in the code above come bundled with Drupal’s Date module which we were already using in the mentioned website). One thing that was pretty clear from the error message was something was wrong with the format of dates stored in start_date/end_date columns of our custom table. And this was strange, we were storing date/times in “YYYY-MM-DD hh:mm:ss” format which is the same format used by Date module to store date time values from its own form fields. And Views had no problem handling those fields, while I was getting lots of above mentioned warnings with Views trying to use date fields from our table. As I was studying Date module’s code, I noticed the module implements some hook and it adds ‘is date‘ property on all date handlers and then it had some code to process only those fields which had ‘is date‘ set to TRUE. So I changed all my handler definitions to add ‘is date‘ property, e.g.  

      'field' => array(
          'handler' => 'views_handler_field_date',
          'click sortable' => TRUE,
	  'is date' => TRUE,
      ),

I then cleared caches and refreshed Calendar page hoping allz well now, only to be greeted by same warnings again. Stumped I spent the following hour and a half studying Date module’s code and googling and making changes to my hook definition but nothing would work. Then I came across this PasteBin code listing a portion of CiviCrm’s code for Drupal. And I saw a ray of hope there. I spent more time reviewing CiviCrm’s Views integration and trying to mimic the same but I would get one or other warnings every time, until taking a cue from CiviCrm, I decided to dump views_handler_field_date and views_handler_filter_date completely in favor of custom field and filter handlers for my table’s date columns. I, in fact borrowed the following handlers from CiviCrm:

1. Field handler: {syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }class workshop_handler_field_datetime extends views_handler_field_date { /* * Convert the DATETIME from the database into unixtime then allow * views_handler_field_date to render as usual. * Also trick php into thinking the time is in the same timezone, no * matter the default timezone */ function render($values) { $value = $values->{$this->field_alias}; if (is_string($value) && strpos($value, “-“)) { $value = strtotime($value); if ($value) { //$date = new DateTime(); //$date->setTimestamp($value); $values->{$this->field_alias} = $value; } } return parent::render($values); } }{/syntaxhighlighter}
2. Filter handler: {syntaxhighlighter brush: php;fontsize: 100; first-line: 1; }/* * All the comparisons need to be done with SQL DATETIMES and not unixtime */ class workshop_handler_filter_datetime extends views_handler_filter_date { /* * We use strtotime() to accept a wide range of date inputs and then * convert the unixtime back to SQL DATETIME before adding the WHERE clause */ function op_simple($field) { $value = intval(strtotime($this->value[‘value’], 0)); if (!empty($this->value[‘type’]) && $this->value[‘type’] == ‘offset’) { // keep sign $value = time() + sprintf(‘%+d’, $value); } $value = $this->format_date($value); $this->query->add_where($this->options[‘group’], $field, $value, $this->operator); } 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)); $this->query->set_where_group(‘OR’, $this->options[‘group’]); } if ($this->value[‘type’] == ‘offset’) { $now = time(); // keep sign $a = $now + sprintf(‘%+d’, $a); // keep sign $b = $now + sprintf(‘%+d’, $b); } $a = $this->format_date($a); $b = $this->format_date($b); // %s is safe here because strtotime + format_date scrubbed the input $this->query->add_where($this->options[‘group’], $field, $a, ‘>=’); $this->query->add_where($this->options[‘group’], $field, $b, ‘<=’); } function format_date($unixtime) { return date(“Y-m-d H:i:s”, $unixtime); } }{/syntaxhighlighter}

And then I changed my hook_views_data to this:   {syntaxhighlighter brush: as3;fontsize: 100; first-line: 1; }function workshop_views_data() { $data = array(); $data[‘workshop_location’][‘table’][‘group’] = t(‘Workshop’); $data[‘workshop_location’][‘table’][‘join’] = array( // Directly links to node table. ‘node’ => array( ‘left_field’ => ‘nid’, ‘field’ => ‘nid’, ), ); //Definition for other fields here. //For the purpose of blog post, I am only enumerating the date/time fields. $data[‘workshop_location’][‘start_date’] = array( ‘title’ => t(‘Location Start Date’), ‘help’ => t(‘Location Start Date’), ‘field’ => array( ‘handler’ => ‘workshop_handler_field_datetime’, ‘click sortable’ => TRUE, ), ‘filter’ => array( ‘handler’ => ‘workshop_handler_filter_datetime’, ), ‘argument’ => array( ‘handler’ => ‘views_handler_argument_date’, ’empty field name’ => t(‘Undated’), ), ‘sort’ => array( ‘handler’ => ‘views_handler_sort_date’, ) ); $data[‘workshop_location’][‘end_date’] = array( ‘title’ => t(‘Location End Date’), ‘help’ => t(‘Location End Date’), ‘field’ => array( ‘handler’ => ‘workshop_handler_field_datetime’, ‘click sortable’ => TRUE, ), ‘filter’ => array( ‘handler’ => ‘workshop_handler_filter_datetime’, ), ‘argument’ => array( ‘handler’ => ‘views_handler_argument_date’, ’empty field name’ => t(‘Undated’), ), ‘sort’ => array( ‘handler’ => ‘views_handler_sort_date’, ) ); //More fields here. return $data; }{/syntaxhighlighter} And bingo, clearing cache one more time and refreshing the Calendar page gave me the results I needed (thanks CiviCrm!!). If you observe the above handlers, you would notice all they do is inherit Date module’s corresponding handlers and add some code to recognize date times in “YYYY-MM-DD hh:mm:ss” format. What I cannot understand is why native Date module handlers cannot recognize this format when Entity Date fields themselves are stored in this format, good grief!! Anyways, for completeness sake, I have attached the source code for these 2 handlers below (DISCLAIMER: they are verbatim copies of corresponding handlers from CiviCrm). And yes, do not forget to specify these handlers in your module’s .info file (the example below assumes you placed the files for these handlers in “views” sub-directory inside your module’s main directory):

files[] = views/workshop_handler_field_datetime.inc
files[] = views/workshop_handler_filter_datetime.inc