CiviCRM TimeZone Feature in version 5.47 creates sync 'oddities'

[kcristiano]

Test environment

WP - 5.9.1 CiviCRM 5.47.0 Event Organizer - 3.10.8 CiviCRM EO - master

CiviCRM now has a TZ option on events.

If your WP Time zone matches the vent timezone, sync fromm CiviCRM to EO is fine.

However, if you set a CiviCRM TZ to any other than the default WP timezone we get the following:

• CiviCRM Event TZ - America/Los_Angeles
• CiviCRM Event Time 3pm
• WP Timezone - America/New_York
• Event Organiser time - 6pm

This is local time based on WP Timezone.

This is actually expected as EO does not have Timezone support.

If I save a new time in WP, it is not syncing to CiviCRM - this is not expected. I will do more testing.

Edit: If you check the 'sync' checkbox the event will sync from WP to CiviCRM. However, times do not sync properly. I would expect the WP time to become the CiviCRM time regardless of TZ. I am not seeing the times update at all.

I wanted to flag this up now and I'll add more comments as I dig into it further.

[christianwach]

@kcristiano Thank you for testing! Some testing from me in response:

WordPress/EO to CiviCRM

if you set a CiviCRM TZ to any other than the default WP timezone we get the following:

• CiviCRM Event TZ - America/Los_Angeles
• CiviCRM Event Time 3pm
• WP Timezone - America/New_York
• Event Organiser time - 6pm

This is local time based on WP Timezone.

This is actually expected as EO does not have Timezone support.

As you say, this looks fine for the time being - the conversion sets the correct time as expected in the WordPress/EO context. I don't see what more I can do until EO supports timezones.

CiviCRM to WordPress/EO

If I save a new time in WP, it is not syncing to CiviCRM - this is not expected. I will do more testing.

Hmm, I don't see this.

Edit: If you check the 'sync' checkbox the event will sync from WP to CiviCRM. However, times do not sync properly. I would expect the WP time to become the CiviCRM time regardless of TZ. I am not seeing the times update at all.

Here's what happens when I create an event in EO and sync it to CiviCRM. It looks like all the event data syncs fine - except for the time:

In EO, with WordPress set to Europe/London:

In CiviCRM the time appears to be an hour off:

If I then change the time of the EO event, e.g.

In CiviCRM, the time is updated, but still appears to be one hour off:

When I look at the CiviCRM event in the API Explorer, I get the following (with some irrelevant data snipped):

Array (
  [id] => 11
  [title] => EO Event
  [event_title] => EO Event
  [summary] => Blah.
  [description] => Blah.
  [event_description] => Blah.
  [event_type_id] => 4
  [is_public] => 1
  [start_date] => 2022-03-30 20:00:00                     <-- Note incorrect times
  [event_start_date] => 2022-03-30 20:00:00
  [end_date] => 2022-03-30 21:00:00
  [event_end_date] => 2022-03-30 21:00:00
  [created_date] => 2022-03-09 13:57:17
  [event_tz] => Europe/London
)

When I query the CiviCRM event via the API in my code, I get:

Array (
  [id] => 11
  [title] => EO Event
  [event_title] => EO Event
  [summary] => Blah.
  [description] => Blah.
  [event_description] => Blah.
  [event_type_id] => 4
  [is_public] => 1
  [start_date] => 2022-03-30 21:00:00                     <-- Note correct times
  [event_start_date] => 2022-03-30 21:00:00
  [end_date] => 2022-03-30 22:00:00
  [event_end_date] => 2022-03-30 22:00:00
  [created_date] => 2022-03-09 13:57:17
  [event_tz] => Europe/London
)

You can see the effect of this on the EO "Edit Event" page, where the "Edit Events in CiviCRM" metabox lists the CiviCRM event with the correct time(s).

I wonder if this mismatch is due to CiviCRM displaying the event with incorrect conversion of the time in both the UI for the event and the API Explorer?

[christianwach]

Update to the above: well, times and timezones are confusing... it turns out that I should check my daylight savings dates... 2022-03-30 21:00:00 is actually after British Summer Time kicks in on 27th March 2022. Oops! 🤦

This means CiviCRM is actually storing the correct time adjusted for BST. If I change the date of the EO event to 18th March 2022, all is well on my test install.

The discrepancy in the return values from the API via code and via the API Explorer is still unaccounted for, however.

[christianwach]

So currently CEO treats EO as though it does have timezone support via the WordPress setting and so it converts "raw" CiviCRM datetimes when syncing to EO. It does not however convert datetimes when syncing to CiviCRM.

It looks like an approach that converts in both directions is needed when CiviCRM 5.47 is detected.

[christianwach]

On further investigation, it appears the situation is this:

• CiviCRM inherits its timezone from WordPress for all date operations
• This means unaltered WordPress date/times must be passed to the CiviCRM API
• CiviCRM stores the WordPress date/time in the database
• There is therefore no point in applying the timezone of the CiviCRM Event to the date/time to be saved
• CiviCRM converts the WordPress date/time to the assigned timezone when displayed in the UI
• The value returned from the CiviCRM API is the one to save to the EO Event
• When displaying CiviCRM Event date/times, $date->setTimezone( $timezone ); must be applied
• It may also be necessary to show the timezone for the CiviCRM Event if it is not the WordPress timezone

More to follow, no doubt.

[christianwach]

Full investigation and solution here.

tl;dr it was a mistake for CiviCRM to change *_date columns in civicrm_event to TIMESTAMP. Reverting them to DATETIME fixes the Daylight Saving offset problems.

[christianwach]

Here’s how CiviCRM/EO/CEO works pre-CiviCRM 5.47:

• Event "datetime" values are stored in EO as DATE and TIME values based on eo_get_blog_timezone()
• Event "datetime" values are stored in CiviCRM as DATETIME values using the WordPress timezone - which is also eo_get_blog_timezone()
• Event "datetime" values are synced to CiviCRM DateTime fields unaltered, i.e. using eo_get_blog_timezone()
• Event "datetime" values are synced to EO Event "datetime" fields using eo_get_blog_timezone()

Here’s how CiviCRM/EO/CEO should work post-CiviCRM 5.47:

• Event "datetime" values are still stored in EO as DATE and TIME values based on eo_get_blog_timezone()
• CiviCRM’s civicrm_event table TIMESTAMP *_date fields must be reverted to DATETIME
• Event "datetime" values in CiviCRM should be stored using the GMT/UTC timezone (with additional event_tz param)
  • HOWEVER: should Event "datetime" values be sent to CiviCRM as GMT/UTC or relative to event_tz?
  • This is a policy decision for the CiviCRM API maintainers and must be adhered to or bad things will happen
• Event "datetime" values in CiviCRM should be read unaltered by MySQL (which happens with DATETIME) as values based on GMT/UTC timezone
  • Event "datetime" values can then be converted as needed using the additional event_tz param

[christianwach]

Closing since timezones in CiviCRM have been punted to a future version.