CiviCRM Community Forums (archive)

We have been having recent concerns about civimails going out too late due to the timezone of the user who created them & the timezone on the server being different.

In order to figure out how to deal with that I used a secret & highly-effective problem-solving techique ... I asked Tim who fished out some links about CiviCRM timestamp fields & shared a few thoughts.

Playing around with this over the last couple of hours it seems surprisingly simple. Mysql has a field type called 'timestamp' - data in this field is stored in UTC but when you run a query against it is converted using the current mysql time_zone variable (hence at first I thought nothing had changed when I converted the field :-).

I converted the datetime fields in the mailing & mailing job queries & saw no discernable difference in the data or CiviCRM behaviour. MySQL was seamlessly converting the data using the server settings.

However, by changing the time_zone for the connection I could see the date in a different time when I did a select. I added the small bit of (not very graceful) code to civicrm.module to detect the drupal timezone & set it in the civicrm connection & it seemlessly 'handled' the timezone issue.

I also tested sending mail from the cronjob  - at first I thought I had to set the timezone in the script scenario - but I realised that as long as it comes up with some form of valid timezone that's good enough - because it's stored in UTC.

So, in summary - the patch below solves the problem for fields converted to timestamp & has no impact if no fields are converted. (ie. I'm making the case for it going in 4.1 so I don't have to manage yet another 4.2 patch for the 4.1 cycle.)

Another happy thing is that if you make the column type change 'early' it doesn't matter as it won't kill your upgrade script in the way that adding an index or a table early does.

Index: drupal/civicrm.module
===================================================================
--- drupal/civicrm.module (revision 37365)
+++ drupal/civicrm.module (working copy)
@@ -270,7 +270,16 @@
 
         // initialize the system by creating a config object
         $config = CRM_Core_Config::singleton();
-     
+
+
+      global $user;
+      $hour = $user->timezone / 3600;
+      $timeZoneOffset = sprintf("%02s:00",$hour);
+      if($timeZoneOffset > 0){
+        $timeZoneOffset .= '+';
+      }
+       $sql = "SET time_zone = '$timeZoneOffset'";
+       CRM_Core_DAO::executequery($sql);   
         // Add module-specific header elements
         drupal_set_html_head(civicrm_html_head());
         require_once 'CRM/Core/BAO/Preferences.php';


ALTER TABLE `civicrm_mailing`
CHANGE COLUMN `created_date` `created_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'Date and time this mailing was created.' AFTER `created_id`,
CHANGE COLUMN `scheduled_date` `scheduled_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'Date and time this mailing was scheduled.' AFTER `is_archived`,
CHANGE COLUMN `approval_date` `approval_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'Date and time this mailing was approved.' AFTER `approver_id`;

ALTER TABLE `civicrm_mailing_job`
CHANGE COLUMN `scheduled_date` `scheduled_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'date on which this job was scheduled.' AFTER `mailing_id`,
CHANGE COLUMN `start_date` `start_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'date on which this job was started.' AFTER `scheduled_date`,
CHANGE COLUMN `end_date` `end_date` TIMESTAMP NULL DEFAULT NULL COMMENT 'date on which this job ended.' AFTER `start_date`;

Thanks for that!!! 

I thought we implemented the sopa filter to remove all jokes about warm beers at the last sprint?

In response to a PM I received today...

We are running timezone-fields on CiviMail and civievent using the technique described here. We are using the technique on versions from 3.4.8 to 4.2 on d6 & d7 and have been doing so for about 18 months without any obvious problems. The process for implementing it is simply to add a bit of code for civicrm.module and then convert fields as desired to timestamp fields - it then respects the drupal timezone settings. The storage within the database is in UTC.

http://issues.civicrm.org/jira/browse/CRM-9683

In order to get it into core the main issues are determining appropriate code for WordPress & Joomla - having said that defaulting to server time would do no harm as the storage is in UTC, testing, figuring out how to convert fields, testing the conversion and removing any php level timezone handling. I also think that you need to switch a bunch of functions to use mysql NOW() rather than php NOW(). It may be easier to only convert certain fields in the first instance.

The fix is the same on any datetime field - alter the field to being a timestamp field and use civicrm.module to set your timezone so you see stuff in the right timezone.

If you look at the above ticket I have been setting the timezone in drupal specific code - it's probably similar to do something like this in Joomla! (http://forum.joomla.org/viewtopic.php?p=2210541#p2347762)

Then you also need to convert the mysql fields from datetime to timestamp. I found this easy & it's pretty safe because if an upgrade script later tries to do it it doesn't fail if already done.
 

Take a look at this thread: http://forum.civicrm.org/index.php/topic,25625.msg123722.html#msg12372

Hope it helps.

BT