Data stored in universal time does not handle DST consistently

• Overview
• Example use-case
• Current behavior
• Proposed behavior 1: Fix MySQL timezones
• Proposed behavior 2: Change format. Use only PHP TZs.
• Comments

Overview

CiviCRM has a number of TIMESTAMP columns -- these are stored in universal time (UTC) and displayed in the user's timezone. However, there is a subtle error in handling Daylight Savings Time (DST): if the current-date and the target-date sit on different sides of the DST-switch, then the time may present as +/- 1 hour.

This bug was one of the major subissues identified in #2122. Although that particular feature was rolled-back/deferred from 5.47, the DST bug still exists -- it's just less obvious.

Example use-case

1. Create a contact record.
2. View the contact record. Note the creation time (civicrm_contact.created_date).
3. Change the system clock - set to a date where DST differs (eg if today is March 30, then go to December 5).
4. View the contact record. Note the creation time (civicrm_contact.created_date).

Current behavior

The displayed value of civicrm_contact.created_date appears to change by +/- 1 hour, depending on when you view it.

(Viewed on Mar 31, 2022)

(Viewed on Dec 5, 2022)

Why? CiviCRM sends a note to MySQL about the current user's timezone (SET time_zone = '...'). However, it doesn't identify the timezone effectively. It gives the current numeric offset (at the moment of viewing) - but (in locales with DST) the offsets fluctuate over time.

(Ex: On Mar 31, the offset in California is -0700. Under current/long-standing law, the offset will be -0800 on Dec 5. Of course, the US Congress is reconsidering this law... so we don't really know what the offset will be!)

Proposed behavior 1: Fix MySQL timezones

CiviCRM should send the timezone as a symbolic name, such as Europe/Helsinki, America/Los_Angeles, or Australia/Sydney. These symbolic-names have an underlying database which allows them adjust automatically based on DST-rules/target-dates/current-law. On the surface, the fix is extremely simple:

diff --git a/CRM/Utils/System/Base.php b/CRM/Utils/System/Base.php
index a4660834c5..8e40f6da35 100644
--- a/CRM/Utils/System/Base.php
+++ b/CRM/Utils/System/Base.php
@@ -755,10 +755,9 @@ abstract class CRM_Utils_System_Base {
    * Set timezone in mysql so that timestamp fields show the correct time.
    */
   public function setMySQLTimeZone() {
-    $timeZoneOffset = $this->getTimeZoneOffset();
-    if ($timeZoneOffset) {
-      $sql = "SET time_zone = '$timeZoneOffset'";
-      CRM_Core_DAO::executequery($sql);
+    $timeZone = $this->getTimeZoneString();
+    if ($timeZone) {
+      CRM_Core_DAO::executequery('SET time_zone = %1', [1 => [$timeZone, 'String']]);
     }
   }

There are a couple of catches.

• Timezone rules change (occasionally). Any software that supports timezones ultimately needs a data feed with current rules. The good news: IANA publishes a free/open feed (https://www.iana.org/time-zones; aka tzdata; aka zoneinfo), most Linux/Unix distros have this feed, and MySQL can read it (mysql_tzinfo_to_sql). It usually requires one command (which could run during system-config, system-startup, and/or cron):

  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql

  The problem: we have no measures for (a) how many CiviCRM deployments actually subscribe to this feed and (b) how many could subscribe, if they chose to.

• Timezone names may be inconsistent (occasionally). For example, in different contexts, it's been fashionable to refer to California's timezone as America/Los_Angeles, US/Pacific, and PST8PDT. (The current+official fashion is America/Los_Angeles - the others are deprecated.) However, since Civi integrates with various layers (different CMSs; PHP APIs; MySQL APIs), there are edge-case where the layers may choose different names. (I'm not super-concerned, but we should raise sensible warnings when names are invalid or mismatched.)

The central issue is - how to cope when data isn't available? This comes to mind:

• (Status check) If the active TZ (getTimeZoneString()) has a deprecated name (eg PST8PDT) or an offset (eg -0700), show a warning.
• (Status check) If the active TZ (getTimeZoneString()) isn't supported by MySQL, show a warning.
• (Runtime) If the active TZ (getTimeZoneString()) isn't supported by MySQL, fallback to sending offset.

Proposed behavior 2: Change format. Use only PHP TZs.

(This expands on one of @haystack's suggestions in #2122.)

If you assume that MySQL time services aren't available - what else would you do? You could use PHP time services.

The astute observer will note the status-quo (using both PHP and MySQL time-services) creates two points-of-failure. If either PHP or MySQL has bad/incomplete/old timezone data, then you'll get mis-calculations somewhere. Consolidating on PHP time-services would reduce the #dependencies.

Both Drupal and WordPress take this approach. (I suspect this is extremely useful for maximizing compatibility with heterogeneous web-hosts.) They each do it a bit differently, but some central concepts are the same:

• In Drupal, PHP processes read+write temporal data in universal time -- as an INT (Unix-style, seconds-since-epoch).
• In WordPress, PHP processes read+write temporal data in universal time -- as a DATETIME with a _gmt suffix (eg post_modified_gmt).
• Hypothetically, you could hardcode MySQL to SET time_zone='+0:00'. PHP processes would read+write temporal data in universal time -- as a TIMESTAMP.

In all those cases, the onus is on the PHP devs to convert to/from universal-time when implementing functionality (eg "find records from March 1 - March 15" or "find records from this afternoon" or "extract the hour:minute component").

But there is a catch here: Civi already relies on several MySQL time-services. The schema works a certain way; the reports/searches/UIs/APIs expect the schema to work a certain way; etc.

The central issue is - how do you manage/QA all the changes (in schema+logic) required to change time-service?

Comments

• I haven't tested, but I'm fairly certain there will be another manifestation in CiviMail scheduling. Ex:
  • You live in a timezone where DST changes on March 16.
  • On March 10, you schedule a mail-blast for 2:00pm on March 20. It stores the schedule with the wrong offset.
  • When March 20 comes, the mailing actually goes out at 3:00pm (or maybe 1:00pm).