How are timezones handled in CiviCRM?

Question

We were running into issues with how CiviCRM activities are showing in Drupal Calendar (Views). Drupal is adjusting the provided activity date/time based on the user configured time zone. For us, this is desired behavior because we have users crossing multiple time zones who want to see the time the activity is executed in whatever time zone they are in.

After a little research (and finding these threads: https://issues.civicrm.org/jira/browse/CRM-9683,
https://forum.civicrm.org/index.php/topic,23411.msg98329.html#msg98329, What timezones should we use on the server and database?, though there are many more tangentially related ones) it appears that CiviCRM assumes that all users will be in the same timezone and stores activity date/times as the local time into the database. Drupal expects that the stored times are in UTC, hence our issue. Would a "simple" fix of converting the activity datetime column format to a timestamp format in mysql be a reasonable fix? I'm worried that we're going to run into this in multiple places...

Thanks!

As an aside, I think it would make sense as a future change for CiviCRM to respect the Drupal user configurable time zones.

Answer 1

For us, there were a couple of changes that made this to work. First, in the database, the civicrm activity_date_time type was modified to be a timestamp. This made it so that when CiviCRM saved the date and time it was stored in the database as UTC once the Drupal timezone was also set to UTC (but users are able to select their own timezone and it behaves appropriately). According to developers in the CiviCRM IRC, this change can be made for any CiviCRM date time type. We also set our OS time to UTC. With just these changes, the Calendar View was still not displaying correctly because it was being adjusted twice. To fix that, we set tz_handling to 'none' in the function civicrm_date_views_fields in civicrm.views.inc.

Hope it helps somebody else!