I have been trying to create a filter for a custom field with the type Unix Timestamp. The filter is supposed to filter results on date values that is between two dates. My problem is that the filter i have set up for this doesn't treat the field as a date. What can i do to tell views that this field is supposed to be treated as a date, similar to the node created field?

I figured out how to do it. I created a module that hooks into views_data_alter and set the filter handler for my custom field.

function custom_module_views_data_alter(&$data) {
  $data['table_name']['field_name']['filter']['handler'] = 'views_handler_filter_date';
  return $data;
}

I don't know if this is the best solution but i works.

Comments

dawehner’s picture

Status: Active » Postponed (maintainer needs more info)

Can you please explain a bit more things?

What do you mean with "this doesn't treat the field as a date"?
In general a views export always helps!

masu0105’s picture

When i look at the SQL query that views generates i can se that the dates in the WHERE clause is displayed as text for example "WHERE date_value BETWEEN '2010-01-01' AND '2011-12-12" what i need is for these dates to be converted into timestamps. If i create a filter for node created these values will automatically get converted to timestamps. My guess is that i somehow must tell views that this field is supposed to be treated as a date and not integer.

Here comes the views export.

$view = new view;
$view->name = 'test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Test';
$view->core = 7;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Test';
$handler->display->display_options['use_more_text'] = 'mer';
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Återställ';
$handler->display->display_options['exposed_form']['options']['exposed_sorts_label'] = 'Sortera efter';
$handler->display->display_options['exposed_form']['options']['sort_asc_label'] = 'Stigande';
$handler->display->display_options['exposed_form']['options']['sort_desc_label'] = 'Fallande';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['pager']['options']['expose']['items_per_page_label'] = 'Inlägg per sida';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all_label'] = '- Alla -';
$handler->display->display_options['pager']['options']['expose']['offset_label'] = 'Kompensera';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'node';
/* Fält: Innehåll: Titel */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 0;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 0;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Sorteringskriterium: Innehåll: Datum för inlägg */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filterkriterium: Innehåll: Publicerad */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filterkriterium: Innehåll: Typ */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'fiskekort_rapport' => 'fiskekort_rapport',
);
/* Filterkriterium: Innehåll: Avser datum (field_fiskekort_rapport_datum) */
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['id'] = 'field_fiskekort_rapport_datum_value';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['table'] = 'field_data_field_fiskekort_rapport_datum';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['field'] = 'field_fiskekort_rapport_datum_value';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['operator'] = 'between';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['expose']['operator_id'] = 'field_fiskekort_rapport_datum_value_op';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['expose']['label'] = 'Avser datum (field_fiskekort_rapport_datum)';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['expose']['operator'] = 'field_fiskekort_rapport_datum_value_op';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['expose']['identifier'] = 'field_fiskekort_rapport_datum_value';
$handler->display->display_options['filters']['field_fiskekort_rapport_datum_value']['expose']['multiple'] = FALSE;

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'test';
$translatables['test'] = array(
t('Master'),
t('Test'),
t('mer'),
t('Verkställ'),
t('Återställ'),
t('Sortera efter'),
t('Stigande'),
t('Fallande'),
t('Inlägg per sida'),
t('- Alla -'),
t('Kompensera'),
t('Avser datum (field_fiskekort_rapport_datum)'),
t('Page'),
);

masu0105’s picture

Status: Postponed (maintainer needs more info) » Active
masu0105’s picture

Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Siripong’s picture

Title: Creating filter for Unix Timestamp field » both Date (Unix Timestamp) and (ISO format)
Version: 7.x-3.0-rc1 » 7.x-3.0
Status: Closed (fixed) » Active

Exactly what the issue opener stated.
My env is Drupal 7.10 + postgresql.
When I add a custom date field and make it exposed,
it not show the

Value type
==========
[] A date in any machine readable format. CCYY-MM-DD HH:MM:SS is preferred. 
...

When I tested to fill data such as '2011-12-31'
with the 'Is equal to' operator
(the field is Date Unix timestamp),
I got
SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "2011-12-31" LINE 7: ...ield_data_field_test_date.field_test_date_value = '2011-12-3...

dawehner’s picture

Can you please export your view, so it's much easier to reproduce the problem?
You know trying to reproduce a bug, with some knowledge of views in the brain is damn hard. You always only do what works ;)

Siripong’s picture

Title: both Date (Unix Timestamp) and (ISO format) » Creating filter for Unix Timestamp field

I create a new view in order to minimize the code.

$view = new view;
$view->name = 'test_date_field_expose_fillter';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Test Date Field Expose Fillter';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['label'] = '';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 0;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 0;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: date_iso (field_date_iso) */
$handler->display->display_options['filters']['field_date_iso_value']['id'] = 'field_date_iso_value';
$handler->display->display_options['filters']['field_date_iso_value']['table'] = 'field_data_field_date_iso';
$handler->display->display_options['filters']['field_date_iso_value']['field'] = 'field_date_iso_value';
$handler->display->display_options['filters']['field_date_iso_value']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_date_iso_value']['expose']['operator_id'] = 'field_date_iso_value_op';
$handler->display->display_options['filters']['field_date_iso_value']['expose']['label'] = 'date_iso (field_date_iso)';
$handler->display->display_options['filters']['field_date_iso_value']['expose']['operator'] = 'field_date_iso_value_op';
$handler->display->display_options['filters']['field_date_iso_value']['expose']['identifier'] = 'field_date_iso_value';
$handler->display->display_options['filters']['field_date_iso_value']['expose']['required'] = 0;
$handler->display->display_options['filters']['field_date_iso_value']['expose']['multiple'] = FALSE;
/* Filter criterion: Content: date_timestamp (field_date_timestamp) */
$handler->display->display_options['filters']['field_date_timestamp_value']['id'] = 'field_date_timestamp_value';
$handler->display->display_options['filters']['field_date_timestamp_value']['table'] = 'field_data_field_date_timestamp';
$handler->display->display_options['filters']['field_date_timestamp_value']['field'] = 'field_date_timestamp_value';
$handler->display->display_options['filters']['field_date_timestamp_value']['exposed'] = TRUE;
$handler->display->display_options['filters']['field_date_timestamp_value']['expose']['operator_id'] = 'field_date_timestamp_value_op';
$handler->display->display_options['filters']['field_date_timestamp_value']['expose']['label'] = 'date_timestamp (field_date_timestamp)';
$handler->display->display_options['filters']['field_date_timestamp_value']['expose']['operator'] = 'field_date_timestamp_value_op';
$handler->display->display_options['filters']['field_date_timestamp_value']['expose']['identifier'] = 'field_date_timestamp_value';
$handler->display->display_options['filters']['field_date_timestamp_value']['expose']['multiple'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'abc' => 'abc',
);

Please let me know if you need more information.

Siripong’s picture

Status: Active » Fixed

sorry for my clumsiness

drush en -y date_views

solve this issue

hass’s picture

Status: Fixed » Active

I guess I have the same issue. I created a date filter that should filter out all values of 0, but the validator does not allow me to create a filter with value 0. It looks like views does not allow unix time stamps as date!? I switched to numeric now, but I think it's the wrong way.

sepehr.sadatifar’s picture

@Siripong There isn't a module called "date_views" so how this drush command solves the problem?
in addition to this I want to use timestamp for `created date ` Contextual filter, is it possible?

dagmar’s picture

date_views is included in the Date module.

sepehr.sadatifar’s picture

@dagmar tnx , how silly of me

damiankloip’s picture

Status: Active » Fixed
hass’s picture

Status: Fixed » Active
merlinofchaos’s picture

Status: Active » Fixed

Put in 1 Jan, 1970 00:00 GMT (or the properly formatted equivalent) to get a date of 0.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

Anonymous’s picture

Issue summary: View changes

Solved.