date facet gives wrong counts

Seems like you have already fixed this yourself, or implemented a work-around. If the former, please share! If the latter, please see whether you can reproduce the bug with some minimal setup locally and reply.

I'm having a similar problem

It gives to right count if you configure the facet to unlimited, reason is that the count code only works on the returned facet items, while it should work on all facet items (query_type_date.inc)

It works for node create/changed date, but not for other date fields.

I had this problem with date created.
Following recomendations I try to set "unlimited"
configure facet display>global options>hard limit
this shows the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1 OFFSET 0' at line 2

on my view:

$view = new view;
$view->name = 'articulos';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'search_api_index_default_node_index';
$view->human_name = 'articulos';
$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['title'] = 'articulos';
$handler->display->display_options['use_more_text'] = 'más';
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Reiniciar';
$handler->display->display_options['exposed_form']['options']['exposed_sorts_label'] = 'Ordenar por';
$handler->display->display_options['exposed_form']['options']['sort_asc_label'] = 'Ascendente';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '12';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['expose']['items_per_page_label'] = 'Elementos por página';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all'] = 0;
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all_label'] = '- Todos -';
$handler->display->display_options['pager']['options']['expose']['offset_label'] = 'Desplazamiento';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'entity';
$handler->display->display_options['row_options']['view_mode'] = 'teaser';
/* Campo: Indexed Nodo: ID del nodo */
$handler->display->display_options['fields']['nid']['id'] = 'nid';
$handler->display->display_options['fields']['nid']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['fields']['nid']['field'] = 'nid';
/* Criterio de ordenación: Indexed Nodo: Fecha de creación */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filtro contextual: Indexed Nodo: Cabecera */
$handler->display->display_options['arguments']['field_cabecera']['id'] = 'field_cabecera';
$handler->display->display_options['arguments']['field_cabecera']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['arguments']['field_cabecera']['field'] = 'field_cabecera';
$handler->display->display_options['arguments']['field_cabecera']['exception']['value'] = '1';
$handler->display->display_options['arguments']['field_cabecera']['default_argument_type'] = 'fixed';
$handler->display->display_options['arguments']['field_cabecera']['default_argument_skip_url'] = 0;
$handler->display->display_options['arguments']['field_cabecera']['summary']['format'] = 'default_summary';
/* Criterios de filtrado: Indexed Nodo: Tipo de contenido */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'article' => 'article',
);
/* Criterios de filtrado: Indexed Nodo: Estado */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = array(
  1 => '1',
);

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'grid';
$handler->display->display_options['style_options']['columns'] = '2';
$handler->display->display_options['style_options']['fill_single_line'] = 1;
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['row_plugin'] = 'entity';
$handler->display->display_options['row_options']['view_mode'] = 'teaser';
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['path'] = 'articulos';

/* Display: Bloque */
$handler = $view->new_display('block', 'Bloque', 'block_1');
$handler->display->display_options['defaults']['pager'] = FALSE;
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '5';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['defaults']['style_plugin'] = FALSE;
$handler->display->display_options['style_plugin'] = 'table';
$handler->display->display_options['style_options']['columns'] = array(
  'field_media' => 'field_media',
  'title' => 'title',
);
$handler->display->display_options['style_options']['default'] = '-1';
$handler->display->display_options['style_options']['info'] = array(
  'field_media' => array(
    'align' => 'views-align-center',
    'separator' => '',
    'empty_column' => 0,
  ),
  'title' => array(
    'align' => 'views-align-left',
    'separator' => '',
    'empty_column' => 0,
  ),
);
$handler->display->display_options['style_options']['override'] = 1;
$handler->display->display_options['style_options']['sticky'] = 0;
$handler->display->display_options['style_options']['empty_table'] = 0;
$handler->display->display_options['defaults']['style_options'] = FALSE;
$handler->display->display_options['defaults']['row_plugin'] = FALSE;
$handler->display->display_options['defaults']['row_options'] = FALSE;
$handler->display->display_options['defaults']['fields'] = FALSE;
/* Campo: Indexed Nodo: Media */
$handler->display->display_options['fields']['field_media']['id'] = 'field_media';
$handler->display->display_options['fields']['field_media']['table'] = 'search_api_index_default_node_index';
$handler->display->display_options['fields']['field_media']['field'] = 'field_media';
$handler->display->display_options['fields']['field_media']['label'] = '';
$handler->display->display_options['fields']['field_media']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_media']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_media']['alter']['more_link'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_media']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_media']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_media']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_media']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_media']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_media']['hide_alter_empty'] = 1;
$handler->display->display_options['fields']['field_media']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['field_media']['type'] = 'file_rendered';
$handler->display->display_options['fields']['field_media']['settings'] = array(
  'file_view_mode' => 'media_preview',
);
$handler->display->display_options['fields']['field_media']['delta_limit'] = '1';
$handler->display->display_options['fields']['field_media']['delta_offset'] = '0';
$handler->display->display_options['fields']['field_media']['delta_reversed'] = 0;
$handler->display->display_options['fields']['field_media']['delta_first_last'] = 0;
$handler->display->display_options['fields']['field_media']['field_api_classes'] = 0;
/* Campo: Indexed Nodo: Título */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'search_api_index_default_node_index';
$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']['external'] = 0;
$handler->display->display_options['fields']['title']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['title']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['more_link'] = 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']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['title']['element_default_classes'] = 1;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['hide_alter_empty'] = 1;
$handler->display->display_options['fields']['title']['link_to_entity'] = 1;
$translatables['articulos'] = array(
  t('Master'),
  t('articulos'),
  t('más'),
  t('Aplicar'),
  t('Reiniciar'),
  t('Ordenar por'),
  t('Ascendente'),
  t('Desc'),
  t('Elementos por página'),
  t('- Todos -'),
  t('Desplazamiento'),
  t('ID del nodo'),
  t('.'),
  t(','),
  t('Todo(s)'),
  t('Page'),
  t('Bloque'),
);

Any help?

Thanks!

That error is coming from elsewhere (i think facetapi), search the source for "-1" and change it to "99999999"

Following your recomendation I added a new option in
facetapi.admin.inc
line 556
$hard_limit_options = drupal_map_assoc(array(3, 5, 10, 15, 20, 30, 40, 50, 75, 100,999999));

and it works!

New issue created for unlilmited, see #1626910: Unlimited not working with search_api and search_api_db

Original problem still the same:

It gives to right count if you configure the facet to unlimited, reason is that the count code only works on the returned facet items, while it should work on all facet items (query_type_date.inc)

The error in #5 should be fixed with #1550772: Facet hard limit set to "no limit" causes SQL error. Please try the latest dev version! No need to hack any modules. ;)

Regarding the original problem: you're right, date facets won't really work unless there is no hard limit. Using "unlimited" will be extremely slow, though, for large result sets, so there's some caution necessary there. I hope I alleviated this by also adding a warning to the description of date facets. In any case, they'll now always use no limit for their facet queries.
Please help test the attached patch!

Hum. It seems I already committed this.