CiviCRM Community Forums (archive)

Using CiviCRM 1.7, when sending 'immediately' using CiviMail, the message is scheduled to be sent 'now' (user's time) but with respect to 'now' (server time).  For example, at 2PM my time I send a message immediately, and it's scheduled to be sent at 9PM my time.  I have got no idea of how it's figuring this out; it doesn't seem at all intuitive.  Has anyone else had this problem?  It's listed as an issue: http://issues.civicrm.org/jira/browse/CRM-1021.

Is there a chance that PHP is running on a different machine than the database?

Ah!  Yes, that's it.

Could
<?php
$job->scheduled_date = date('YmdHis');
?>be replaced by something that gets the database time?  I'm not familiar with how it would be done within Drupal, but in SQL it'd be
SELECT DATE_FORMAT( NOW( ) , '%Y%m%d%H%i%s' )
In Drupal, would the following work?
<?php
$job->scheduled_date = db_result(db_query("SELECT DATE_FORMAT( NOW( ) , '%Y%m%d%H%i%s' )"));
?>

I filed CRM-1952 to be fixed in CiviCRM 1.8.

my DB server is in the same TZ as the web server!  So the date/times being returned by PHP's date() and MySQL's NOW() should be the same.

Well, are they or are they not? The easiest way to check (if you don’t have shell access) is with phpinfo() for the webserver and a SELECT NOW() query in PHPMyAdmin.

Ok, in this case I need access to your box to investigate. Please email me (my GPG key is D128F14A if you want to encrypt anything) or ping me on IM if you can share the credentials.

We don’t really hook to Drupal’s time information in any way (being as much CMS agnostic as we can, as we also run as a Joomla module), so you can take Drupal time out of the equation.

As you can see in the PHP snipped quoted above, we get the webserver’s current time and put it in the database. Your best bet would be to check what time makes it into the database (civicrm_mailing_job.scheduled_date) and whether this is the time that appears later on the mailings selector.

I saw this topic is related to my issue, so I place it here.

First a question to samwilson: did your code work? How did you make it work, wich file...?

My issue:
My site is for the Netherland, my server is in Site5 (Dalles) so there is a time diff of 7 hours.

I can change my hosting server time in civicrm.settings.php with the code:
ini_set('date.timezone',        'Europe/Amsterdam');
I checked good working for for activities, send test mail in Civicmail and the reported times in CiviMail report/views

It does not work for send immediatly or schedule a mail in civimail. The send time reported is the Europe/Amsterdam time. But the real send time is de Dallas server time. Is there a way to solve this? Maybe CiviMail can read the TZ from the settings.php?

Is this an issue to be solved? For V1.8 or 2.0?



 

I am not a script writer, but I did take the challenge to go into this issue.

Problem 1: When your servertime is not your local time all the time settings (activities, mailing) are server based, not local as wanted.

Problem 2: Registered scheduled send mail time is not the same time as the time is realy send (start date).
I found out that the registered scheduled send mail time (same time as time for activities) is php based and the timing for the start date is mysql based.

Solution 1:  Make your own costum time zone. In civicrm.settings.php I inserted from line 6:

/**
 * Setup your timezone for local timings in civicrm:
 * For the list of the supported timezones see: http://nl3.php.net/manual/nl/timezones.php
 * Settings for the Netherlands:
 *          ini_set('date.timezone','Europe/Amsterdam');
 * Uncommand and fill in the timezone setting when you have timing differences
 */
#ini_set('date.timezone','             ');

You then have location based timing, only the scheduled timings for sending email is not equal. For that we need also solution 2.

Solution 2: The timing for sending e-mails is in file: civicrm/CRM/Mailing/BAO/Job.php.
From line 65 there is our query:
$query = "  SELECT      *
                    FROM        $jobTable
                    WHERE       (start_date IS null
                        AND         scheduled_date <= NOW()
                        AND         status = 'Scheduled')
                    OR          (status = 'Running'
                        AND         end_date IS null)
                    ORDER BY    scheduled_date, start_date";

In line "scheduled_date <= NOW()" is the comparison, NOW() is mysql based. We can make this line php timezone based bij changing the query and bij adding some codes above the query:

/**
 * Get the php zonetime and compare it with the scheduled start time for mailing
 */
$zonetime = date('H,i,s,m,d,Y', strtotime('now'));
$query = "  SELECT      *
                    FROM        $jobTable
                    WHERE       (start_date IS null
                        AND         scheduled_date <= STR_TO_DATE('$zonetime','%H,%i,%s,%m,%d,%Y')   
                        AND         status = 'Scheduled')
                     OR          (status = 'Running'
                        AND         end_date IS null)
                    ORDER BY    scheduled_date, start_date";

That it. The user has the freedom to make his own choice in local time settings and only has to make an extra setting in civicrm.settings.php.
This is only a solution for working with civicrm in 1 timezone, not for working in differend timezones, but solves the time differences.

This code worked for me (mysql 4.2.1 and php 5.2.1) The php code used is for php4, I did not test this.
I don't know effects in other parts of civicrm, I did not noticed this. I think this code is CMS agnostic, maybe this can be tested by others and when approved implemented in the core civicrm1.8?

Sorry, I don't know how to make a patch of this.

Regards
Martin