Event-Time-Zone-Implementation-Planning.md

@@ -10,6 +10,7 @@
     * The __nominal__ date/time perspective would appear on a printed pamphlet when advertising an `Event`. This is expressed in the *event-specific timezone*.
     * The __canonical__ date/time perspective would drive automated processing (notifications, sorting, etc) on a shared/central server. This is expressed in a *universal timezone* (eg UTC/GMT).
     * The __personal__ date/time perspective would appear when you lookup a webinar on the calendar of your personal smartphone. This is expressed based on the attendee's timezone.
+* __Time Zone Database__: A common list of timezones which describes the rules/offsets/quirks used in different geographic regions.
 
 ## Event-TZ v1: Original changes
 
@@ -103,3 +104,26 @@ All of the following components may make use of the dates+times on `civicrm_even
     * QR code check-in (`net.ourpowerbase.qrcodecheckin`)
     * Summary Fields (`net.ourpowerbase.sumfields`)
     * WordPress / Event Organiser
+
+## Appendix: Framework conventions
+
+CiviCRM is an application/framework/platform that integrates with several other applications/frameworks/platforms. Each of these can have different conventions for handling date/time. Here is a quick summary for a few platforms:
+
+* __Unix__: Store _canonical perspective_ as _an integer_ which counts the _number of seconds_ since _January 1, 1970 (midnight UTC/GMT)_.
+* __PHP__:
+    * _Settings_: Provides a _timezone setting_. This can be inherited from the server, or it can be personalized for the active user (`date_default_timezone_set()`).
+    * _Formats_: Supports Unix-style integers (`time()`, `date()`, `strtotime()`) and PHP OOP (`DateTime`, `DateTimeZone`).
+* __MySQL__:
+    * _Settings_: Provides a _timezone setting_. This can be inherited from the server or personalized for the active user (`@@session.time_zone`).
+    * _Formats_: Supports strings of the form `YYYY-MM-DD hh:mm:ss`. Stores these as either `DATETIME` or `TIMESTAMP`.
+        * `DATETIME` is _passive_; it stores and returns the literal value. Perspective is ambiguous (varies by use-case).
+        * `TIMESTAMP` is _active_; it uses the _timezone setting_ to auto-convert between timezones. Stores in _canonical perspective_ and displays with _personalized perspective_.
+* __Drupal__:
+    * _Formats_: Uses the Unix-style integers -- for both PHP interfaces and MySQL schema.
+    * _Settings_: Provides multiple timezone settings -- the _per-site/default TZ_ and the _per-user/personalized TZ_. Generally disregards PHP/MySQL settings.
+* __WordPress__:
+    * _Formats_: Uses MySQL-style strings (`YYYY-MM-DD hh:mm:ss`) and MySQL `DATETIME`. Makes two copies of each value (eg `comment_date`/`comment_date_gmt` and `post_date`/`post_date_gmt`). The first copy respects the per-site timezone setting, and the second copy respects GMT/UTC.
+    * _Settings_: Provides a _timezone setting_ for each site. Generally disregards PHP/MySQL settings.
+* __CiviCRM__:
+    * _Settings_: Does not provide its own timezone setting. Inherits the active timezone from Drupal/WordPress. Propagates the setting to MySQL+PHP. (But has a bug here - it propagates the _current offset_ rather than the _timezone_. This misbehaves when crossing a DST boundary.)
+    * _Formats_: Uses MySQL-style strings (`YYYY-MM-DD hh:mm:ss`) and a mix of MySQL `DATETIME`/`TIMESTAMP`. (`DATETIME` for storing nominal perspective; `TIMESTAMP` for canonical+personal perspectives.)