Datetime is indexed with system timezone (not storage timezone/UTC)

And here would be proposed solution.

So the problem is indeed with datetime typed fields.

Here I have this entity, which has Date field publish_date

Now, it's settings will look like this:

Then I'll index the given field:

Then let's see the database.

Entity create & change times are in timestamp form,

But the Publish date actually is in string form

Now lets see what index looks like

Changed date looks identical:

But now here's the trick, publish date has been converted to timestamp:

Now the thing here is that like in my example code, the Date field uses default storage timezone, which it tells to be UTC, but to actually use the same constant in SAPI would require dependency for the module that provides the field.

For example \Drupal\Component\Datetime\DateTimePlus::createFromTimestamp states following:

The timezone of a timestamp is always UTC. The timezone for a
timestamp indicates the timezone used by the format() method.

So that's why my patch forces UTC in the backend.

The problem that exists in current version is that if system timezone is different than UTC, following happens:
- user enters time (in his/her timezone)
- datetime gets stored in the backend in UTC timezone
- index fetches data from database
- passes data to strtotime()
- strtotime() thinks we got string in system timezone
- now we indexed value that's as much different from the original as the system timezone differs from UTC

Note that whenever datetime is shown to user, it's prepared first with the default timezone of Drupal or the timezone of user itself.

Also note that when I'm referring to system, I'm talking about the machine where Drupal runs.

Yet to mention that current code is this:

      case 'date':
        // Timestamp is numeric. it will be returned as is.
        if (is_numeric($value) || !$value) {
          return 0 + $value;
        }
        // String will be converted to timestamp, timezone gets applied.
        return strtotime($value);

And documentation of strtotime quite explains it shorter than I do:

Each parameter of this function uses the default time zone unless a time zone is specified in that parameter. Be careful not to use different time zones in each parameter unless that is intended. See date_default_timezone_get() on the various ways to define the default time zone.

In my eyes, if any date is stored in database in other timezone than UTC, then it would be developer level mistake. Any user input should be converted anyway.

When date is stored in other timezone than UTC, it's not easy to create filter where date is limited by time (say expiration date or publishing date, so the view would be cutting results from either way from current time), and this is where I met this issue. When stored correctly in UTC, no matter where user, backend or database is located, filtering view by current time will give you correct results.

I tested your patch with Views, as well the Unit test, and can confirm them working. I was unable to run the Kernel test, but didn't find anything suspicious there (phpcs did with DrupalPractice standard though, guess it does not support list()).

All in all, seems good to me.

Looking at the error for example in here: https://www.drupal.org/pift-ci-job/909915 as it says Table 'jenkins_drupal8_contrib_patches_24770.search_api_db_database_search_index_1' doesn't exist:

and my own test says

Table
    'drupal.search_api_db_database_search_index_1' doesn't
    exist:

In my eyes that actually means the simpletest is trying to locate the table from the actual (real and live) database.

That would sound a bit dangerous thought, but I'm looking whether that is the case.

I think you could check whether you accidentally have search index names "database_search_index_1" in your system that you're running tests on.

Edit: Wrong analysis removed :)

Now that I took a look on how other tests had made queries to database, they have surrounded table with {} marks, so apparently it is meant to be executed in real database.

Added curlys to table, removed debugging lines and changed $excepted to $expected