1
  • 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.

  • 3
    What's the timezone set on the server? Is a default timezone set for the Drupal site? If you are logged in as a user, is a separate timezone set for the user? What time does Drupal see with \Drupal::time()->getCurrentTime()? – Patrick Kenny Jan 10 at 15:27
  • Thanks for responding @Patrick Kenny. On the server, timedatectl showed Local time Mon 2021-01-11 09:27:21 MST (my time when I issued the command). Time zone showed America/Denver (my time zone). In Drupal under Regional settings, Default time zone is set to Denver (my time zone) and Users may set their own time zone is off. This code error_log("currentTime=".date('Y-m-d H:i:s', \Drupal::time()->getCurrentTime())); displayed 21-01-11 09:44:19 currentTime=2021-01-11 09:44:19 (the time when I refreshed the page). So, everything looks right. That's why I'm stumped. – Marshall Morrise Jan 11 at 16:52
2

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);
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.