I imported a table using Data module. The table contains a datetime field. In "configure views" all the handlers are automaticaly set to date handlers, but when I'm trying to add a filter on that field, it's not being handled as a date field, i.e.: I'm not getting the options of "extra settings" for a date field as I'm getting when filtering a date field in a node, for example.
This is how the problem looks: please see attached file "filter options for date in node.png"

This is how a date in a node looks (meaning, BTW, that date API module works perfect).
Please see attached file "filter options for date in node.png"

After messing around with it for a whole 3 days and checking every solution that could be, I finally checked the sql query that the view creates, and finally realized that the field is not handled as a date field at all!

While in a well responding date field the code in the query is:

WHERE (( (node.status = '1') AND (node.type IN  ('page')) AND (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(10800)), '%Y-%m-%d') >= '2014-05-07' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(node.created), SEC_TO_TIME(10800)), '%Y-%m-%d') <= '2014-05-08

the code in the imported field (I tried the same dates) is:

WHERE (( (datetry.datecreated BETWEEN 1399410000 AND 1399496400

Meaning: the field is not handled by the right handler. This is not only a UI problem, because even if I'll create my own form and send the parameters - it's still not being handled well in the code.

I tried to follow the code and checked about everyfile that should be included in the process. In data_ui.admin.inc, in the data_ui_date_form function, I found the following comment (lines no. 856-857):

          // TODO: make this required somehow when a date type is selected for
          // the field.

Somehow I have the feeling that here is the problem, the date field is not handled there.

Also, I tried changing the field in the "configure date fields" tab in the data table ui into a "datetime", but this didn't make any change. Besides, I don't know if it's normal or not, but when checking the table schema through the "edit schema" tab the field appears int and there is no date option, although the schema module did recoginze the field as datetime (and apparently the data module too, because the date hanlders where chosen).

Hope you could help me on that. I'm willing to work on the code if needed - just tell me where do I need to make changes.

CommentFileSizeAuthor
#5 data-date-field-is-not-handled-as-a-date-in-views-filter-issue-2262149-comment-number-5.patch467 bytesmmcintosh
filter options for date in node.png22.34 KBtzippy
Filter for date in imported table.png10.95 KBtzippy

Comments

tzippy’s picture

tzippy commented
Issue summary: View changes
tzippy’s picture

tzippy commented
Issue summary: View changes
tzippy’s picture

tzippy commented
Related issues: +#1797168: MySQL data type 'datetime' not supported when importing, editing or creating a table.

I applied the code that appears here MySQL data type 'datetime' not supported when importing, editing or creating a table so now the field appears as "datetime" in "edit schema" tab, means that part is handled well.
The main problem - the query pary - is still not ok.

tzippy’s picture

tzippy commented

SOLVED!
The problem is with the handler, as I said.
By default, the Data module chooses views_handler_filter_date as a filter, and so both the UI and the query are not well handled.

The correct handler to choose is date_views_filter_handler. Once I choose it with the UI in the "cofigure views" tab, it's finally working.
You should also choose the "date type" for the field in "configure date fields" in order for this to work perfect. This is the part that I mentiond in the issue, that has a "TO DO" remark. This is a smaller problem because you can still do it manually.

To fix it for good the code in data.views.inc in function data_get_views_handler should be fixed. instead of (line 286)

case 'filter':
      switch ($schema['fields'][$field_name]['type']) {
        case 'float':
        case 'numeric':
          return 'views_handler_filter_numeric';
        case 'int':
        case 'serial':
          return 'views_handler_filter_numeric';
        case 'datetime':
          return 'views_handler_filter_date'; //this line to remove
      }

Should come:

case 'filter':
      switch ($schema['fields'][$field_name]['type']) {
        case 'float':
        case 'numeric':
          return 'views_handler_filter_numeric';
        case 'int':
        case 'serial':
          return 'views_handler_filter_numeric';
        case 'datetime':
          return 'date_views_filter_handler'; //this is the new line
      }
mmcintosh’s picture

mmcintosh commented
StatusFileSize
new data-date-field-is-not-handled-as-a-date-in-views-filter-issue-2262149-comment-number-5.patch467 bytes

Just in case anyone needs wants to use this I rolled tzippy's fix into a patch against the 7.x-1.x version on this version it is line 305.

This is the single line diff

diff --git a/data.views.inc b/data.views.inc
index b426790..9f096b8 100644
--- a/data.views.inc
+++ b/data.views.inc
@@ -302,7 +302,7 @@ function data_get_views_handler($type, $table, $field_name, $default = FALSE) {
case 'serial':
return 'views_handler_filter_numeric';
case 'datetime':
- return 'views_handler_filter_date';
+ return 'date_views_filter_handler';
}
return 'views_handler_filter_string';

joachim’s picture

joachim commented
Issue tags: -Filter criteria, -datetime

I'm guessing that the date_views_filter_handler handler is provided by Date module. This means the code will crash if Date isn't present, surely?

-Mania-’s picture

-Mania- commented

I have a similar issue but the fix mentioned in this thread doesn't work for me. For now I'm getting around it by using a numeric filter with something like 'greater than 2017-01-01'. Still I'd like to see this working properly with dates!