How to filter Date Views with correctly timezone

Question

I am using latest Drupal 8.2.5 for Live-score website. I have the Views for display all games daily. It's including DateTime for schedule and storing on Europe/London timezone (by default).

On the views FILTER CRITERIA, I added Match_Date_Fields between:

• Today
• +1 day

For getting the matchs from today events and next events. So, the views will filter by default timezone (london), sure. But when the visitor access to my site from Thailand. This filter seem to wrong. Because here difference between timezone from London to Thailand.

For example: The game is play on "January 15, 2017 19:45" for London timezone, but from Thailand it's "January 16, 2017 02:45".

I made it successfully print with depends on the local's time zone. But have no idea How to filter with correctly local's timezone?

Answer 1

Ok, I made it working. My solution is created a custom drupal 8 module with the file: .views_execution.inc inside your module folder.

Inside that, I added function modulename_views_query_alter($view, $query) with:

//Filter by timezone for bettingsafe's local ip
switch ($view->element['#name']){
    case 'league_match':
        $key = 0;
        foreach ($query->where as $key => $value){
            $key_2 = 0;
            foreach ($value['conditions'] as $key2 => $condition){
                $length = strlen('DATE_FORMAT');
                if (substr($condition['field'], 0, $length) === 'DATE_FORMAT'){
                    $condition['field'] = str_replace('DATE_FORMAT(<date field>', 'DATE_FORMAT(CONVERT_TZ(<date field>, <from timezone>, <to timezone>')', $condition['field']);                        
                    $query->where[$key]['conditions'][$key_2]['field'] = $condition['field'];
                }
                $key_2+=1;
            }
            $key+=1;
        }
    break;
}

From my server Centos, I install the tool CONVERT_TZ:

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p<sql root's password>

Restart mysqld and clear Drupal cache.

Hope that's will help someone.