unable to index historical date fields

Ah, I see where the problem is: dates are stored as UNIX timestamps (seconds since 1970) and even though dates before 1970 should be handled correctly, dates long before this result in too high values that can't be indexed anymore.
Sadly, I don't really see a way around this, on the module level. We have to draw a line at some point, and only very few people will want to index dates that far away from 1970.
To fix the bug locally for you, either manually change the database column to have a higher range, or change the field type to one that can handle such high numbers. (Depending on your use cases, this might not have any drawbacks. E.g., indexing a date as "float" should let you sort on it just the same.)

Dates are nevertheless processed as timestamps by the Search API, or rather the database backend, as soon as you set the field type to "date".
I don't know the field definition of ISO date fields (if this is something special), but normally it should expose dates as UNIX timestamps.

Sadly, this won't be that easy. The Search API uses Entity API to extract field data, and in the Entity API all dates are always represented as timestamps. Therefore, the Search API doesn't even get the raw stored value at any time during indexing. Special-casing for this therefore would be a lot of work, very hack-ish and would also defeat the purpose of a generic search module. For your own use, you could easily write a data alteration bringing the date in any format you want, though (and storing it in a new field). Of course, you'd still have to index it as a string …

I agree that Karen makes a pretty good case as to why non-timestamp date fields make a lot of sense. However, only few of these arguments apply to use in the Search API, I think. The only one I can think of are your historical dates, which really are an edge case. So it's not "all these date fields", but only a few of them. And while I agree that setting the type to "String" or "Float" isn't an obvious workaround, it should still work perfectly fine for nearly all cases. I just checked: setting the type to "String" will even index it as a date in ISO format, exactly what you want (and also making sorting work as expected).

When indexing these date fields as strings the indexing does not fail, however, the content fails to be found in the search - so I'm not sure its a realistic workaround. (for example I have a date field storing the year '1889' as the iso date '1889-01-01T00:00:00' and when indexing as a string the appropriate content does not appear with a search of '1889', '1889-01-01', or '1889-01-01T00:00:00').

In that case, date would have been the wrong choice, too, probably. Index the field as fulltext instead and you should be fine.
I think of dates as little more than a way of sorting, and maybe restricting results to a certain range, therefore suggested "String".

Regarding documentation: I don't think many people will run into this, so while there is only a README.txt for documentation, I'd say it would just clutter things up. I plan to add proper documentation in the handbook in a few months, though, and then will hopefully remember to include this in "Known problems"/"Workarounds" or whatever section I'll create for this.

'SearchApiException: Cannot perform fulltext search on field field_death_year of type string. in SearchApiDbService->search() (line 569 of [personal path to sites directory]/sites/all/modules/search_api/contrib/search_api_db/service.inc).'

Huh, this shouldn't happen and seems to be a bug in the database service class. Please file it as a seperate issue, this really should be fixed.

If I choose full text first with a new index it finishes indexing and searching, but does not find the content. (searching for 1889 or any of the variations I specified before finds no content).

If I look at the database the search api tables are storing timestamps. It's storing the positive timestamp of the original date. (e.g. 2429809200 instead of -2429809200 for 1893). So even if I index as full text or string the database is storing it as a timestamp. Probably because the field's metadata still marks it as a 'date' field and the Entity API processes it that way???

Ah, you are right, it's only converted to a proper date string if the selected type is "String", not "Fulltext". Another bug, of course. The attached patch should fix this.

Notes about my personal use case -- I would like to leave the field as date because at other points I want the internal users to be able to specify all content between date x and date y (filtered via a view with exposed date filters), but ideally I'd love to have a keyword search for external users that would support searching specific dates as well as the full text of the other fields.

With the "Aggregated fields" data alteration you can easily copy the contents of a field to a new one (among a lot of more advanced things), allowing you to have basically the same field with multiple types.

I can't imagine that this is really caused by this patch. It's more likely an effect of #1163096: Cached types for DB servers aren't correctly updated.

This works.

But rather than %c which indexes things like "12T15" and it's all just numbers - I'd suggest a better choice would be something like 'Y y F M m j d l D' which gives you things like 2011, January, Jan, Thursday, Thu as well as the numbers.

Then a user can do a more intuitive "Jun 1953" search.

Ah, yes, you are right. Also, this should use format_date() (not date()) and the "%" was of course a mistake.
Patch attached.

Moving this to the new project.

Here is a reroll of the patch against the database project.

Year 2013 and historical dates are not indexed yet. Same problem related here.
Will it get a fix?

The only thing that prevented this from being fixed years ago is the lack of testers. If the attached patch works for you, I can commit it.

Here is a patch that applies some changes:

1. If a new date field is indexed, its type on database will be big int instead of int
2. Updates the old date fields on database to big int format (hook_update_N)

Same patch from #21 with a little code adjustment.

Thanks for the patch, looks good!
However, I'm actually not sure whether we really need to update existing tables. There's always some risk involved in update functions, and stuff should generally continue working as before. If you move a new index to the DB server, you'll get the improvements. If you had problems with historic dates before, then you're going to have to re-index anyways. I guess I'm fine either way, though.

However, the update hook portion of your patch has several problems:

1. +++ b/search_api_db.install
   @@ -81,3 +81,31 @@ function search_api_db_update_7102() {
   +/**
   + * Changes date fields from int to big int. The purpose is give support to historical dates.
   + */
   

   Comment lines shouldn't exceed 80 characters in length, and the first sentence should be separated.
   See documentation standards.

2. +++ b/search_api_db.install
   @@ -81,3 +81,31 @@ function search_api_db_update_7102() {
   +function search_api_db_update_7103() {
   +  $query = db_select('search_api_index', 'sai');      ¶
   +  $query->join('search_api_server', 'sas', 'sai.server = sas.machine_name');
   

   Trailing whitespace (again a few lines down).
   Also, for consistency, I'd prefer if you just used i and s as the table aliases.

3. +++ b/search_api_db.install
   @@ -81,3 +81,31 @@ function search_api_db_update_7102() {
   +  $indexes = search_api_index_load_multiple($indexes);
   

   As far as possible, update hooks shouldn't make use of API functions. It would be preferable if you just loaded all (necessary) fields in the database query – that's just machine name and options anyways.

4. +++ b/search_api_db.install
   @@ -81,3 +81,31 @@ function search_api_db_update_7102() {
   +  foreach ($indexes as $machine_name => $index) {
   +    foreach ($index->options['fields'] as $field => $info) {
   +      if ($info['type'] == 'date') {
   +        $field = str_replace(':value2', '_value2', $field);
   +        $field = str_replace(':value', '_value', $field);
   +        db_change_field('search_api_db_' . $machine_name, $field, $field, $spec);
   +      }
   +    }
   +  }
   

   You shouldn't/can't just guess at the table and column names here. Read them from the server options. (Which should have the type in them, too, come to think of it – so I guess you don't need the indexes at all, just the options for all DB servers.)

Please either fix those, or we'll just go with the no-update route. In any case, we should probably incorporate my patch from above, too. Even though it's only slightly related, don't really know how that ended up here … (I also have no idea why it fails for the test bot.)

Thanks for 'best practices' tips! Here is the updated patch.

Thanks for the improved patch!
However, there are still two issues left:

1. +++ b/search_api_db.install
   @@ -81,3 +81,28 @@ function search_api_db_update_7102() {
   + * Changes date fields from int to big int.
   + * The purpose is give support to historical dates.
   

   There should be a blank line in between these two.

2. +++ b/search_api_db.install
   @@ -81,3 +81,28 @@ function search_api_db_update_7102() {
   +          if ($field['type'] == 'date') {
   +            db_change_field($field['table'], $field['column'], $field['column'], $spec);
   +          }
   

   For indexes on the server before #2083079: Store single-valued fields in a single, denormalized table, 'column' will be empty and should default to 'value'.

Since these are very little problems, I just fixed them myself in the attached patch. Also, as said in my previous post, I included the patch from #19, too. If this new patch works for you (and the test bot), I'll commit it in the next few days.

Hi.

patch #25 worked perfectly.

thank you very much.

Committed.
Thanks again for your work, bember, and thanks for testing @ gilsbert!