Date filter not reading times [#281570]

I'm trying to display a window of events using filters for a start time greater than now -1 hour and an end time less than or equal to now +8 hours. But the query does not appear to be reading the time part of the date -- I either get no results at all, or, if that 9-hour window crosses into the next day, I get results from Day 1 00:00:00 to Day 2 00:00:00.

I'm on PHP 5.

My view:

  $view = new stdClass();
  $view->name = 'tv_schedule';
  $view->description = 'TV Schedule';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'TV Schedule';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'crosstab';
  $view->url = 'tv/schedule';
  $view->use_pager = FALSE;
  $view->nodes_per_page = '0';
  $view->sort = array (
    array (
      'tablename' => 'node_data_field_channel',
      'field' => 'field_channel_value',
      'sortorder' => 'ASC',
      'options' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'created',
      'sortorder' => 'ASC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
    array (
      'type' => 'fulldate',
      'argdefault' => '2',
      'title' => '',
      'options' => '',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
    array (
      'tablename' => 'node_data_field_channel',
      'field' => 'field_channel_value',
      'label' => 'Channel',
      'handler' => 'content_views_field_handler_group',
      'sortable' => '1',
      'defaultsort' => 'ASC',
      'options' => 'default',
    ),
    array (
      'tablename' => 'node',
      'field' => 'created',
      'label' => '',
      'handler' => 'views_handler_field_date_custom',
      'options' => 'H:i',
    ),
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
    array (
      'tablename' => 'node_data_field_episode_title',
      'field' => 'field_episode_title_value',
      'label' => '',
      'handler' => 'content_views_field_handler_group',
      'options' => 'default',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'tv_episodes',
),
    ),
    array (
      'tablename' => 'node_data_field_end_time',
      'field' => 'field_end_time_value_default',
      'operator' => '<=',
      'options' => 'now +8 hours',
      'value' => '',
    ),
    array (
      'tablename' => 'node_data_field_start_time',
      'field' => 'field_start_time_value_default',
      'operator' => '>',
      'options' => 'now -1 hour',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node_data_field_channel, node, node_data_field_episode_title, node_data_field_end_time, node_data_field_start_time);
  $views[$view->name] = $view;

My start time field:

Widget: Text Field with jquery pop-up calendar
Label: Time
Default value: Blank
Input format: 11/07/2008 16:02:40
Years back and forward: -3:+3
Time increment: 1
Position of date part labels: Above
Display in group: No group
Help text:
Required: TRUE
Multiple: Never
To date: Never
Granularity: Year, Month, Day, Hour, Minute
Date display: Friday, July 11, 2008 - 4:02 pm
Time zone handling: No time zone handling
Repeat display: Expanded

End time field

Widget: Text Field with jquery pop-up calendar
Label: End Time
Default value: Blank
Input format: 11/07/2008 16:02:40
Years back and forward: -3:+3
Time increment: 1
Position of date part labels: Above
Display in group: No group
Help text:
Required: FALSE
Multiple: Never
To date: Never
Granularity: Year, Month, Day, Hour, Minute, Second
Date display: Friday, July 11, 2008 - 4:02 pm
Time zone handling: No time zone handling
Repeat display: Expanded

The query as displayed by the Devel module

SELECT node.nid, node_data_field_channel.field_channel_value AS node_data_field_channel_field_channel_value, node.created AS node_created_created, node.created AS node_created, node.title AS node_title, node.changed AS node_changed, node_data_field_episode_title.field_episode_title_value AS node_data_field_episode_title_field_episode_title_value FROM {node} node LEFT JOIN {content_type_tv_episodes} node_data_field_end_time ON node.vid = node_data_field_end_time.vid LEFT JOIN {content_field_start_time} node_data_field_start_time ON node.vid = node_data_field_start_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_channel ON node.vid = node_data_field_channel.vid LEFT JOIN {content_type_tv_episodes} node_data_field_episode_title ON node.vid = node_data_field_episode_title.vid WHERE (node.status = '1') AND (node.type IN ('tv_episodes')) AND (node_data_field_end_time.field_end_time_value <= '2008-07-12 00:02:12') AND (node_data_field_start_time.field_start_time_value > '2008-07-11 15:02:12') ORDER BY node_data_field_channel_field_channel_value ASC, node_created_created ASC

Please let me know if you need any additional information or if there's anything I can do to help.

Comments

Comment #1

KarenS CreditAttribution: KarenS commented 13 July 2008 at 11:39

Status:

Active

» Postponed (maintainer needs more info)

I just committed a number of fixes to the date filter. They're available now in cvs and will be in the tarball in another 12 hours or so. Try the latest code, I'm pretty sure all the filter problems are ironed out there.

Comment #2

jesss CreditAttribution: jesss commented 14 July 2008 at 15:52

I updated the module this morning, cleared the views cache, disabled my browser cache, and edited and saved the view but I'm still not getting the right results.

Here's this morning's query.

SELECT node.nid, node_data_field_channel.field_channel_value AS node_data_field_channel_field_channel_value, DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H%m') AS node_created_orderby, node.created AS node_created, node.title AS node_title, node.changed AS node_changed, node_data_field_episode_title.field_episode_title_value AS node_data_field_episode_title_field_episode_title_value FROM {node} node LEFT JOIN {content_field_start_time} node_data_field_start_time ON node.vid = node_data_field_start_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_end_time ON node.vid = node_data_field_end_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_channel ON node.vid = node_data_field_channel.vid LEFT JOIN {content_type_tv_episodes} node_data_field_episode_title ON node.vid = node_data_field_episode_title.vid WHERE (node.status = '1') AND (node.type IN ('tv_episodes')) AND (node_data_field_start_time.field_start_time_value > '2008-07-14 11:23:19') AND (node_data_field_end_time.field_end_time_value <= '2008-07-14 19:23:19') ORDER BY node_data_field_channel_field_channel_value ASC, node_created_orderby ASC

I'm not sure this affects this particular issue, but shouldn't (FROM_UNIXTIME(node.created), '%Y%m%d%H%m') be (FROM_UNIXTIME(node.created), '%Y%m%d%H%i')?

Here's what *does* affect my view. If I take this query, remove the curly brackets, then plug it directly into mySQL, I get no results. If, however, I change (node_data_field_start_time.field_start_time_value > '2008-07-14 11:23:19') AND (node_data_field_end_time.field_end_time_value <= '2008-07-14 19:23:19') to (node_data_field_start_time.field_start_time_value > '2008-07-14T11:23:19') AND (node_data_field_end_time.field_end_time_value <= '2008-07-14T19:23:19') I get the results I'm expecting. This suggests to me that there may be something off in how Drupal and/or mySQL is processing the date format.

Comment #3

KarenS CreditAttribution: KarenS commented 14 July 2008 at 17:24

When I create a query like this, I get something like:

SELECT DISTINCT(node.nid), node_data_field_date.field_date_value AS node_data_field_date_field_date_value, node.created AS node_created_created, node.title AS node_title, node.changed AS node_changed, node_data_field_date.field_date_value2 AS node_data_field_date_field_date_value2 FROM node node LEFT JOIN content_type_dates node_data_field_date ON node.vid = node_data_field_date.vid WHERE (node.type IN ('dates')) AND (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value2, '%Y-%m-%dT%T'), SEC_TO_TIME(-14400)), '%Y-%m-%d\T%H:%i') <= '2008-07-14T21:20') AND (DATE_FORMAT(ADDTIME(STR_TO_DATE(node_data_field_date.field_date_value, '%Y-%m-%dT%T'), SEC_TO_TIME(-14400)), '%Y-%m-%d\T%H:%i') >= '2008-07-14T17:20') GROUP BY node.nid, node_data_field_date_field_date_value, node_created_created ORDER BY node_data_field_date_field_date_value ASC, node_created_created ASC LIMIT 0, 10

which is correct and produces the right results. I did commit more fixes this morning, although I wouldn't have said our queries would look so different.

The (FROM_UNIXTIME(node.created), '%Y%m%d%H%m') you note above is not coming from the Date module, that's created by the Views module for the node created date sort. However the Views code does not create anything like that in the D5 version, that looks more like code it creates in the D6 version. Are you sure you're using the D5.2 version and not the D6 version of the Date module? I do *not* have the filters working right yet in the D6 version, I'm moving to that once I get the D5 version stabilized.

You also have a 'fulldate' argument that could be affecting your query, but that is not a Date module field and I don't know what it is.

Comment #4

jesss CreditAttribution: jesss commented 14 July 2008 at 18:11

We're on Views 5.x-1.6 and Date 5.x-2.x-dev. I can assure you that we're not running *any* D6 modules -- we're building solely on Drupal 5.

The 'fulldate' argument is based on Node: Posted Full Date. I've removed it, but I'm still not getting any results.

SELECT node.nid, node_data_field_channel.field_channel_value AS node_data_field_channel_field_channel_value, DATE_FORMAT(FROM_UNIXTIME(node.created), '%Y%m%d%H%m') AS node_created_orderby, node.created AS node_created, node.title AS node_title, node.changed AS node_changed, node_data_field_episode_title.field_episode_title_value AS node_data_field_episode_title_field_episode_title_value FROM {node} node LEFT JOIN {content_field_start_time} node_data_field_start_time ON node.vid = node_data_field_start_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_end_time ON node.vid = node_data_field_end_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_channel ON node.vid = node_data_field_channel.vid LEFT JOIN {content_type_tv_episodes} node_data_field_episode_title ON node.vid = node_data_field_episode_title.vid WHERE (node.status = '1') AND (node.type IN ('tv_episodes')) AND (node_data_field_start_time.field_start_time_value > '2008-07-14 14:07:04') AND (node_data_field_end_time.field_end_time_value <= '2008-07-14 22:07:04') ORDER BY node_data_field_channel_field_channel_value ASC, node_created_orderby ASC

Comment #5

jesss CreditAttribution: jesss commented 16 July 2008 at 18:48

Seeing the note on the Date project page, we've updated to the latest CVS of Views (and updated the Date module again, for good measure). Our query still doesn't look anything like yours, though, and the view is still exhibiting the same behavior. Are you using a -dev version of Views?

SELECT node.nid, node_data_field_start_time.field_start_time_value AS node_data_field_start_time_field_start_time_value, node_data_field_channel.field_channel_value AS node_data_field_channel_field_channel_value, node.title AS node_title, node.changed AS node_changed, node_data_field_episode_title.field_episode_title_value AS node_data_field_episode_title_field_episode_title_value FROM {node} node LEFT JOIN {content_type_tv_episodes} node_data_field_end_time ON node.vid = node_data_field_end_time.vid LEFT JOIN {content_field_start_time} node_data_field_start_time ON node.vid = node_data_field_start_time.vid LEFT JOIN {content_type_tv_episodes} node_data_field_channel ON node.vid = node_data_field_channel.vid LEFT JOIN {content_type_tv_episodes} node_data_field_episode_title ON node.vid = node_data_field_episode_title.vid WHERE (%s.%s %s '%s') AND (%s.%s IN ('%s')) AND (node_data_field_end_time.field_end_time_value <= '2008-07-16 22:45:50') AND (node_data_field_start_time.field_start_time_value > '2008-07-16 13:45:50') ORDER BY node_data_field_start_time_field_start_time_value ASC, node_data_field_channel_field_channel_value ASC

Args

Array
(
    [0] => node
    [1] => status
    [2] => =
    [3] => 1
    [4] => node
    [5] => type
    [6] => tv_episodes
)

Comment #6

KarenS CreditAttribution: KarenS commented 16 July 2008 at 20:47

I have been switching back and forth between the latest Views official release and the-dev version to try to make sure things will work right in both. There is no way I can produce a query like you're getting with the code I'm using with either version of Views.

What kind of field are you using (date or datestamp)? That's not clear from the info you posted above.

And where are you getting your code -- from the tarball or cvs? Can you copy the id info from the top of the date_api_sql.inc and date_views.inc files so I can try to figure out which version of the code you have.

Comment #7

jesss CreditAttribution: jesss commented 16 July 2008 at 20:59

We're using the date field, not the datestamp.

We're getting the code from the tarball (whatever's been posted to the Date project page). We are completely replacing the module each time to ensure there aren't old files hanging around. Here's the info.

date_api_sql.inc

// $Id: date_api_sql.inc,v 1.4.2.23 2008/07/15 14:13:25 karens Exp $

date_views.inc

// $Id: date_views.inc,v 1.26.2.35 2008/07/15 13:56:20 karens Exp $

Comment #8

jesss CreditAttribution: jesss commented 21 July 2008 at 19:05

Status:

Postponed (maintainer needs more info)

» Fixed

We figured this out. There was an old, duplicate version of the date module in our modules folder that was causing a conflict. Once we tracked that down and removed it, our queries started looking right, and we were getting the results we expected. Sorry for the confusion!