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
