I have a custom table with a field "ShowDate" of type date. I have a custom module that maps this table as shown below. I am able to create a view and list all of the existing data but I need to filter by date like "now -1 day". This filter is setup as ">= now -1 day" but all of the data shows. An example of thjs field in the table is "2023-03-18" and this shows in the view.
function signup_views_data() {
$data = array();
$data['ShowsNotices']['table']['group'] = t('ShowsNotices');
$data['ShowsNotices']['table']['base'] = array(
'field' => 'ShowNoticeID', // This is the identifier field for the view.
'title' => t('Shows and Notices'),
'help' => t('Shows and Notices'),
'weight' => -10,
);
$data['ShowsNotices']['ShowNoticeID'] = array(
'field' => array(
'handler' => 'views_handler_field',
'click sortable' => TRUE, // This is use by the table display plugin.
),
'title' => t('ID'),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['ShowsNotices']['Place'] = array(
'title' => t('Place'),
'help' => t('Just a plain text field.'),
'field' => array(
'handler' => 'views_handler_field',
'click sortable' => TRUE, // This is use by the table display plugin.
),
'sort' => array(
'handler' => 'views_handler_sort',
),
'filter' => array(
'handler' => 'views_handler_filter_string',
),
'argument' => array(
'handler' => 'views_handler_argument_string',
),
);
$data['ShowsNotices']['ShowDate'] = array(
'title' => t('Show Date'),
'help' => t('Date of Show.'),
'field' => array(
'handler' => 'views_handler_field_date',
'click sortable' => TRUE,
),
'sort' => array(
'handler' => 'views_handler_sort_date',
),
'filter' => array(
'handler' => 'views_handler_filter_date',
),
);
return $data;
}
Comments
I've moved this to How To and Troubleshooting as more people will see it here. I've also put the code in a PHP code block to make it more readable.
Hi harpsw. When you say "This filter is setup as ">= now -1 day" do you mean that you entered the operator ">=" in the actual "Relative date" filter? That will not work. You need to select the operator from the dropdown menu.
Also, question. In your table definition, what type of column is ShowDate? varchar? datetime?
This is the filter for the field. The mysql database has ShowDate as a "date"
Are you able to do some debugging? Can you output the variables in
views_handler_filter_date:op_simple()?Specifically, I would enable Devel and insert something like
Just to check the values that are being used for the sql expression.
Good call on the devel...it shows the filter on the date field is looking for a unix timestamp. FYI, that value corresponds to yesterday, July 13
WHERE (( (ShowsNotices.ShowDate >= 1752418017))
The database values are like this: "2025-03-18"
Any ideas on how to work thru this?
Yes, what I suspected. The core views filter handler for dates (
views_handler_filter_date) can only handle UNIX timestamp sql columns.You'll either need to extend that filter handler to add another expression that converts your date string into a timestamp, or you can modify your column to store timestamps.
The date field filter (
date_views_filter_handler_simple) can give you clues on how to extend the core handler to deal with date strings.I think storing as timestamps would be the simplest and best way. Thanks for the help!!
Just FYI, for field API date fields that use ISO format (basically a string), the resulting expression produced by the date_views_filter_handler_simple handler is
DATE_FORMAT(STR_TO_DATE(field_data_field_a_date.field_a_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2025-07-13'Your extension of
views_handler_filter_dateshould produce a similar expression.