Exposed date filter against >= “today” fails until 5pm Mountain time

Question

• I added a Date only date field named expires to the user profile.
• The expires field has the value 2021-01-08 for a certain user (I checked the database; it has no time component).
• In a view, I have an exposed filter that tests the expires field using Operator=Is greater than or equal to with Value type=An offset from the current time and Value=today.
• On 2021-01-09 up until 5pm Mountain time, the user with expires = 2021-01-08 is improperly included in the view.
• When I refresh the view at 5pm Mountain time or thereafter, the user is dropped from the view.
• I tried switching to Value type=A date in any machine readable format and Value=today. This also has a problem, but in this case users aren't included when they should be sometimes (the problem is sort of reversed).
• If, as a test, I edit the exposed filter and use Value type=A date in any machine readable format and Value=2021-01-09 (i.e. hardcode the date) then the user with expires=2021-01-08 is not included in the view at any time, as expected.

It appears that today isn't giving me today's date until 5pm Mountain Time. What am I missing?

SOLUTION: Inspired by @Patrick Kenny below, I added this to MYMODULE.views_execution.inc:

function MYMODULE_views_query_alter($view, $query)
{
    if (!empty($query->where[1]['conditions'])) {
        foreach ($query->where[1]['conditions'] as $key => &$value) {
            error_log($value['field']);
        }
    }
}

Testing on 2021-01-13 at 10:17 am Mountain Time with Operator=Is greater than or equal to and Value type=An offset from the current time and Value=today, the output is

DATE_FORMAT(user__field_expire.field_expire_value, '%Y-%m-%d') >= DATE_FORMAT('2021-01-12T17:00:00', '%Y-%m-%d')

Notice the today value of 2021-01-12T17:00:00. WRONG DAY. Off by 7 hours. This confirms the answer by @Patrick Kenny.

HOWEVER, if I change to Value=now (instead of Value=today) and test again, the output is

DATE_FORMAT(user__field_expire.field_expire_value, '%Y-%m-%d') >= DATE_FORMAT('2021-01-13T10:17:02', '%Y-%m-%d')

Notice the today value of 2021-01-13T10:17:02. CORRECT DAY and time.

In my case, switching to now seems adequate to fix the problem because the Y-m-d format is applied, making the comparison work correctly. But I still subscribe to @Patrick Kenny's solution as a general fix.

Answer 1

Since the server timezone, Drupal timezone, and \Drupal::time()->getCurrentTime() are all pointing to the same time, you have probably been bitten by the same bug in Views that I was, Date/time Views filter tries strotime() relative to Unix epoch.

Basically, all relative values like "today" "yesterday - 5 hours", etc. can have timezone issues in Views. There's another related issue I reported as well. This bug has yet to be fixed.

In my case, I needed to filter by "midnight of the current day", and Views was giving me incorrect results depending on the timezone.

I fixed it like this (code more or less pasted directly from my site, but it should give you an idea of what you can do):

function MYMODULE_views_pre_view(ViewExecutable $view, $display_id, array &$args) {
  if (($view->id() === 'VIEW_MACHINE_NAME') &&
    ($display_id === 'page_1')) {
    $filters = $view->display_handler->getOption('filters');
    if ($filters['field_MY_FIELD']) {
      $user_timezone = date_default_timezone_get();
      $current_timestamp = \Drupal::time()->getCurrentTime();
      $midnight_usertimezone_datestring = MYMODULE_get_midnight_datestring("$current_timestamp", MYMODULE_get_utc_offset($user_timezone));

      $filters['field_MY_FIELD']['value']['value'] = $midnight_usertimezone_datestring;
    }
    $view->display_handler->overrideOption('filters', $filters);
  }
}