... | @@ -8,7 +8,7 @@ |
... | @@ -8,7 +8,7 @@ |
|
* (_Ex: "Under the laws of 2021, the `US/Central` timezone uses an offset `-0600` during winter; during summer, it uses `-0500`."_)
|
|
* (_Ex: "Under the laws of 2021, the `US/Central` timezone uses an offset `-0600` during winter; during summer, it uses `-0500`."_)
|
|
* __Time Perspective__: Dates and times are subjective; the same date/time can present with different values depending on one's timezone. While there are many possible perspectives on an `Event`, we define three key perspectives:
|
|
* __Time Perspective__: Dates and times are subjective; the same date/time can present with different values depending on one's timezone. While there are many possible perspectives on an `Event`, we define three key perspectives:
|
|
* The __nominal__ date/time perspective would appear on a printed pamphlet when advertising an `Event`. This is expressed in the *event-specific timezone*.
|
|
* 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 __universal__ 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.
|
|
* 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.
|
|
* __Time Zone Database__: A common list of timezones which describes the rules/offsets/quirks used in different geographic regions.
|
|
|
|
|
... | @@ -29,7 +29,7 @@ The TIMESTAMP conversion was done so that the front-end interface would require |
... | @@ -29,7 +29,7 @@ The TIMESTAMP conversion was done so that the front-end interface would require |
|
|
|
|
|
## Event-TZ v2: Proposed solution
|
|
## Event-TZ v2: Proposed solution
|
|
|
|
|
|
The start and end dates have traditionally provided the _nominal perspective_ (*the date/time advertised on a printed pamphlet*). These fields should stay in their original form (`DATETIME`, nominal). Additionally, new columns should (1) store the nominal timezone (`event_tz`) and (2) store the canonical time. This has a few merits:
|
|
The start and end dates have traditionally provided the _nominal perspective_ (*the date/time advertised on a printed pamphlet*). These fields should stay in their original form (`DATETIME`, nominal). Additionally, new columns should (1) store the nominal timezone (`event_tz`) and (2) store the universal time. This has a few merits:
|
|
|
|
|
|
* It provides backward-compatibility for [relevant screens/tools/APIs](#affected) -- by returning the start and end dates in the same way. (_If you call `Event.get +w id=4 +s start_date` in v5.30 and v5.60, you should get the same value._)
|
|
* It provides backward-compatibility for [relevant screens/tools/APIs](#affected) -- by returning the start and end dates in the same way. (_If you call `Event.get +w id=4 +s start_date` in v5.30 and v5.60, you should get the same value._)
|
|
* Screens may be updated incrementally to give richer information (using any of the columns, depending on the downstream goals+preferences).
|
|
* Screens may be updated incrementally to give richer information (using any of the columns, depending on the downstream goals+preferences).
|
... | @@ -110,7 +110,7 @@ All of the following components may make use of the dates+times on `civicrm_even |
... | @@ -110,7 +110,7 @@ All of the following components may make use of the dates+times on `civicrm_even |
|
|
|
|
|
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:
|
|
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)_.
|
|
* __Unix__: Store _universal perspective_ as _an integer_ which counts the _number of seconds_ since _January 1, 1970 (midnight UTC/GMT)_.
|
|
* __PHP__:
|
|
* __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()`).
|
|
* _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`).
|
|
* _Formats_: Supports Unix-style integers (`time()`, `date()`, `strtotime()`) and PHP OOP (`DateTime`, `DateTimeZone`).
|
... | @@ -118,7 +118,7 @@ CiviCRM is an application/framework/platform that integrates with several other |
... | @@ -118,7 +118,7 @@ CiviCRM is an application/framework/platform that integrates with several other |
|
* _Settings_: Provides a _timezone setting_. This can be inherited from the server or personalized for the active user (`@@session.time_zone`).
|
|
* _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`.
|
|
* _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).
|
|
* `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_.
|
|
* `TIMESTAMP` is _active_; it uses the _timezone setting_ to auto-convert between timezones. Stores in _universal perspective_ and displays with _personalized perspective_.
|
|
* __Drupal__:
|
|
* __Drupal__:
|
|
* _Formats_: Uses the Unix-style integers -- for both PHP interfaces and MySQL schema.
|
|
* _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.
|
|
* _Settings_: Provides multiple timezone settings -- the _per-site/default TZ_ and the _per-user/personalized TZ_. Generally disregards PHP/MySQL settings.
|
... | @@ -127,7 +127,7 @@ CiviCRM is an application/framework/platform that integrates with several other |
... | @@ -127,7 +127,7 @@ CiviCRM is an application/framework/platform that integrates with several other |
|
* _Settings_: Provides a _timezone setting_ for each site. Generally disregards PHP/MySQL settings.
|
|
* _Settings_: Provides a _timezone setting_ for each site. Generally disregards PHP/MySQL settings.
|
|
* __CiviCRM__:
|
|
* __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.)
|
|
* _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.)
|
|
* _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 universal+personal perspectives.)
|
|
|
|
|
|
## Related Tasks
|
|
## Related Tasks
|
|
|
|
|
... | | ... | |