Views date field/timezone breaks in timezones that have varying offset, e.g. BST

Also getting this issue.

For example if you have an exposed date and you have a timezone with saving time (for example America/Halifax which is -3 or -4 depending on time) is always INTERVAL -14400 regardless of time of year.

This seems to be an issue because getTimezoneOffset in the QueryPluginBase uses the current time to get the offset, rather than the requested time via the exposed date.

Hi,

Bumping into this one too.

Would it make sense to add a timezone override in the Advanced => Query options so we can set timezones for specific views?

This would cover my use case where most views are fine, but a couple are to do with bookings saved in UTC which the view should use.

Cheers

Tom

This is normal based on the documented priority definitions, especially since the IS mentions a workaround.

> I have worked round it by overriding \Drupal\views\Plugin\views\query\Sql

A workaround that involves creating your own custom Views query plugin is a pretty hardcore solution that's going to be way out of the reach of many developers.

I don't think that's what the issue priority definitions mean by a workaround, so I'd say this should be a major at least.

Fair enough! Updated.

I just closed #3057780: Views QueryPluginBase::getTimezoneOffset doesn't take into account DST for dates other then now as a duplicate of this issue. What do you think of keeping the title from that issue?

This bug led to #3207086: [HEAD BROKEN] Consistent failure in MonthDatePluginTest. I started looking into that and found my way to getTimezoneOffset(). Then I searched the issues for mentions of that function, which is how I found this issue and #3057780.

I am attaching a test-only patch that proves the bug. I am setting the status to NR to trigger a test. Unless the testbot is mad at me, it should set the status back to NW. The failing test is Drupal\Tests\views\Functional\Plugin\MonthDatePluginTest::testMonthDatePlugin.

I found another duplicate: #3181657: Views date timezone handling is broken for zones with DST. That issue also has a failing test, and a different proposed resolution. I am copying it into the issue summary here.

I already added a note to the issue summary that we should test with MySQL, PostgreSQL, and SQLite when we think we have a fix. I do not think that we have to exercise all three on the test-only patch.

Just wanted to chime in here with the situation where I ran into issues relating to this. Whilst the originally reported issue is not quite the same, the other tickets that mention the root cause I ran into were closed as duplicates of this issue; so I'm placing this here.

In my case the root cause was due to \Drupal\views\Plugin\views\query\QueryPluginBase::getTimezoneOffset() as also reported in #3057780 and #3181657

Let me describe the confluence of circumstances that triggered the issue for me:
- The datetime entered in the CMS is in the user's timezone (Europe/Amsterdam) in this case
- The datetime is for an all day event, which means the entered time is set to 00:00:00 (this is date_recur's handling of all day events)
- The datetime is properly stored in the database as UTC
- It is currently winter time
- The date we are filtering the view on is in summer time

Due to the timezone offset being calculated being incorrect (only 3600 seconds (1 hour) interval is added to the database values in the query) all day events would show a day early when in winter time and looking at summertime events.

Our solution was to create a custom filter that extends the datetime Date filter, do our own timezone offset calculation (based upon the filter value) and disable the original calculation.

Since it was mentioned that creating a plugin might be out of reach for many developers, I'll share our solution here so it can be used as a starting point for others:

namespace Drupal\custom_event\Plugin\views\filter;

use Drupal\datetime\Plugin\views\filter\Date;
use Drupal\views\Attribute\ViewsFilter;
use Drupal\views\Plugin\views\query\Sql;

#[ViewsFilter("custom_event_schedule")]
final class EventScheduleFilter extends Date {

  /**
   * @see \Drupal\views\Plugin\views\query\QueryPluginBase::getTimezoneOffset()
   * @link https://www.drupal.org/project/drupal/issues/2923131
   * @link https://www.drupal.org/project/drupal/issues/3057780
   * @link https://www.drupal.org/project/drupal/issues/3181657
   */
  protected function opSimple($field): void {
    $query = $this->query;
    assert($query instanceof Sql);

    $date_value = $this->value['value'];
    if ($this->options['granularity'] == 'year') {
      $date_value = preg_replace('/^(\d{4})$/', '$1-01-01', $date_value);
    }

    $dtz = new \DateTimeZone(date_default_timezone_get());
    $dt = new \DateTime($date_value, $dtz);
    $offset = $dtz->getOffset($dt);

    // Replace default offset calculation.
    // The default calculation is based on 'now' instead of input value.
    $this->calculateOffset = FALSE;
    $query->setFieldTimezoneOffset($field, $offset);

    parent::opSimple($field);
  }

}

and then overriding the specific field's filter via the module file:

/**
 * Implements hook_views_data_alter().
 */
function custom_event_views_data_alter(array &$data): void {
  assert(isset($data['date_recur__node__event__schedule']['event__schedule_value']['filter']['id']));
  $data['date_recur__node__event__schedule']['event__schedule_value']['filter']['id'] = 'custom_event_schedule';
}