<solved>Timestamps reported by MySQL and PHP are mismatched

[marchelo2212]

Hello everyone ...
I installed CiviCRM and at first it was all right but a few months ago this message is appearing me ...

"Reported by MySQL Timestamps (eg" 03/09/2014 10:31 ") and PHP (eg" 9/3/2014 9:31 ") are mismatched."

The correct time is to load from Joomla PHP (I think) ... my time zone is -5 gtm (Guayaquil / Ecuador - Bogota / Colombia).

Has someone happens something similar ..? .. How I can fix it?

Thanks a lot!

Marcelo

[Erik Hommel]

There are some helper function in CRM/Utils/Date.php, you could use those functions?

[Michael McAndrew]

A couple of questions.

Did this appear after an upgrade?

Have you checked how the timezones are set up in your MySQL and PHP configuration?

This check was introduced around April this year (see https://github.com/civicrm/civicrm-core/commits/6978ebe07ecc2336ab9b12ee9872d81ef961c740/CRM/Utils/Check/Env.php for details if you want).

I think you need to ensure that MySQL and PHP are using the same timezone.

[marchelo2212]

Hi..!! thanks a lot for your answers!

Yes, I made an upgrade version and appearing this message.

I don't know where is the problem, if is when php transform "timestamps":
administrator/components/com_civicrm/civicrm/CRM/Utils/System/Base.php.

this is a several problem..? some time ago don't show me any message, and I have not changed any settings.

[Michael McAndrew]

http://wiki.civicrm.org/confluence/display/CRMDOC/checkMysqlTime gives some detail of what to look for (linked in that warning message).  There are three possible problems

    PHP and MySQL may be running on different servers, and the clocks may be out-of-sync. Configuring automatic clock synchronization is the best solution.
    PHP, MySQL, and/or the operating system may be configured to use different default timezones. Verify the configuration of each.
    The content management system (Drupal, Joomla, or WordPress) or one of its plugins may manipulate the timezone settings without CiviCRM's awareness. You may wish to post to the CiviCRM Forum about your problem. Please include any available details about the timezone settings in the operating system, PHP, MySQL, and the CMS; if you have any special CMS plugins or configuration options which may affect timezones, please report them.

So it could be any three of the above. Who does your hosting?  Is it a server/VPS/similar that you have configured? Or is it a third party service with a cpanel/webmin type interface?

That would help us work out which of the above is more likely. Do you have a sysadmin that is able to help with this?

[Upperholme]

I've seen this message on one my sites recently. Interestingly I have several Civi installs on the same VPS and they don't all show the notice, which i would expect them to as they are using the same resources.
Would be good to include some guidance for non-sysadmins as to how go about resolving this issue, if indeed it is an issue.

[Dan C]

I have had the same thing, one site of several in the same environment showing the message.  It turned out that site had a customized php.ini file that was overriding the default timezone on the servers PHP install (it was a legacy site that we inherited).  Unfortunately when that is the problem, there isn't a way to fix it other than editing the php.ini file.  This is one of those problems that unfortunately typically requires a sysadmin to fix, unless it is a case where the timezone is being set by the CMS in which case it can probably be fixed through the administrator interface of the CMS.

[marchelo2212]

according civicrm the possibilities about this problem are ...
Some plugins can modify the time line...... well I installed a new Joomla with civi but the messaje (time MySQL and PHP) continue.
Other possibility is MySQL and PHP are in different server... In my case, I think no because these are in the same server (I am correctly).

My hosting service is shared, I have a cpanel interface, I sent a message to the support service, these are the messagess:
Support:
We were able to set the desired time for PHP by adding the following line to your global php.ini:
date.timezone = "America/Guayaquil"

An .htaccess was also placed in the /www/www/web folder with the following line in it:
SetEnv TZ
Now, the CiviCRM reports the following message:
Timestamps reported by MySQL (eg "2014-09-02 13:22") and PHP (eg "2014-09-02 12:22" ) are mismatched.
Read more about this warning

Basically, the CiviCRM is loading the server's default MySQL time, which is in the EDT time zone, while the PHP has been correctly set to America/Guayaquil.

You should be able to resolve the issue by correcting the time zone in your CiviCRM installation. Unfortunately, as this installation is in Spanish, we were not able to locate the setting in question.

Me
I searched the time zone option but CIVICRM don't have this, only can modify the format date.
CiviCRM has my time zone, because the message has my time (Joomla - PHP).
You can change the time zone in MySQL..??

Suppor
Unfortunately, the default time zone of the MySQL server cannot be changed. Actually, the time zone of MySQL should not matter. A properly working script would simply convert the timestamps when reading/writing data to the MySQL database. CiviCRM should be automatically doing this as well. You could ask on their forums for the reasons why this warning is displayed. Are the timestamps displayed incorrectly in CiviCRM?
We found code that takes care of automatically converting timestamps with the required time zone offset in /home/prenatal/www/www/web/administrator/components/com_civicrm/civicrm/CRM/Utils/System/Base.php.

well.... can you help me...?? What are the lines to modify..?
my file php.ini has these lines:
date.timezone = "America/Guayaquil"   

danc   can you show me the lines of your php.ini...?

[Dan C]

I had to remove the date.timezone setting from the php.ini file so that the PHP and MySQL timezones would match (so I can't show you it because it doesn't exist).  However, that is basically what support told you.  If your MySQL timezone is Eastern like they say then your php.ini line would need to be date.timezone = "America/New_York" which would force PHP to match the MySQL server and the error will disappear. 

I'm not sure why Civi requires the two to match instead of forcing them to.  I'm sure it was thought out but someone who was part of the discussion should chime in on that.

[marchelo2212]

danc
 I did not removed the time zone but your words help me....

Olny change the time zone_:
date.timezone = "America/Guayaquil"
to
date.timezone = "America/New_York"
Now the problem is solved.

Thanks a lot
Marcelo

[bcambon]

Hi,

Following your instructions to solve this problem,
and adding information especially for wordpress/civixrm users...

"php.ini" is this file:
/www/wp-settings.php

You have to change "date_default_timezone_set( 'UTC );" by your localisation
eg: date_default_timezone_set( 'Europe/Paris' );

Hope it's useful for some of you.

This instruction seems to solve my problem.

Bests regards

[tristanbradley]

Drupal 7.43
CiviCRM 4.7.2

Server Time EST
My Time UTC (GMT in London)

This was driving me a bit potty - managed to fix it by going to:
/admin/config/regional/settings

and setting the Default Time Zone to UTC (right at the bottom of the list) - fixed everything for me.

Might be worth a try.

[vtitarchuk]

I had similar issue. It was solved, but here is the story.

MySql time stamp was posted properly. Then I checked the php.ini and corrected the config setting to my time zone.

The issue did not go away until I properly set the time in the WordPress settings: wp-admin/options-general.php

MAMP 3.5
CiviCRM 4.7
WordPress 4.5

[drupleg]

Drupal 7.43
CiviCRM 4.7.2

Server Time EST
My Time UTC (GMT in London)

This was driving me a bit potty - managed to fix it by going to:
/admin/config/regional/settings

and setting the Default Time Zone to UTC (right at the bottom of the list) - fixed everything for me.

Might be worth a try.

I had a similar problem, but this didn't solve it for me because, while my Drupal site was set to the proper time zone, the site regional config allowed "Users may set their own time zone.". The problem for me was that I, the logged-in admin user, had my time zone inadvertently set to UTC. So CiviCRM was reading my Drupal user time when, for example; creating bulk mailings. I would create a mailing an 12:00 (my actual local time, and server time), yet the "Draft/Scheduled Mailings" in CiviCRM showed it was created at 5:00PM/17:00! This was because my Drupal user profile was indeed set to UTC.

The lesson being - if all of your server tzdata, localtime, PHP, and MySQL settings are correct, and you're still having problems - be sure your Drupal user timezone is also set properly.  Hope this helps someone else, this one was quite a pain for me to finally track down.

--Tony