Today I had the challenge of generating a view that needed to filter results based on the date/time a node was created. More specifically, I needed to filter bowling stats for a season. Easy, right? The problem I ran into is that the bowling season doesn't begin and end in the same year. So using an argument to only grab nodes created in 2011 didn't work. I needed the view to grab nodes between July, 2010 through July, 2011.
Initially I experimented with Views Date Range. Alas, this module doesn't span multiple years.
Next, I thought I could use a php filter in the views admin area, so I downloaded Views PHP Filter. After toying with the module a bit, I realized that this wasn't going to work either since this filter only accepts a PHP array of node id's.
Finally, after searching Google for longer than I'd like to admit, I realized an easy/canned solution didn't exist, so I began researching the Views API and came across the hook function: hook_views_query_alter(). The hook_views_query_alter() function has limited documentation, so I wrote a custom module and inserted the hook function to see what it would do.
The hook_views_query_alter() function has the $query variable passed to it. This variable is an object that contains all the parameters for Views to build the query including tables, relationships, and what I was interested in, the where clause.
I built my view and added a filter on the Node: Post Date. In the settings on the filter, I selected the 'Is Between' operator and added a default minimum and maximum date. The dates I choose were irrelevant as I planned on altering these with the prior mentioned hook function.
In my custom module, I added the hook_views_query_alter() function and did a print_r on the $query variable. After viewing the object's structure, I quickly found my filter in the where section of the object. Next, I dynamically created my timestamps and replaced the $query objects default timestamps. When I checked the view, all the proper nodes were being displayed.
Hooray hook_views_query_alter!
