Synchronizing PHP and MYSQL timezones

While working on my new project, a personal finance web app I started to encounter weird time issues.

The problem was the timezones of php and the data retrieved from a MySQL database were out of sync so the if statement would always evaluate to false. The thing is, although both php and mysql use UTC by default, MySQL only stores date/time data with UTC timezone, when data is retrieved from the database MySQL uses the server's timezone.

So how to fix this issue? If by some chance your php timezone settings are different from UTC it's pretty straightforward just call:

default_time_zone_set("UTC");

//or

ini_set("date.timezone", "UTC")

If you don't want to change you settings from code just change your php.ini manually. To set your MySQL default "retrieval" timezone you will need to change MySQL's my.cnf file, it is usually located in /etc/ on Centos or /etc/mysql/  folder on Ubuntu server. Just put one of these two in the file. You need to put them as they are, without quotation marks.

default_time_zone=+00:00  #or  default_time_zone=UTC

Do know that if you go with the first choice it will not take into account daylight savings (winter and summertime). This is important if you need to calculate dates for the future, but if you're going to use only past dates you can go with this. If you go with the second choice you will need to run a command beforehand.

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Type it in the terminal and hit enter as hard as you can! This command loads timezone string names into the mysql database. To check if everything is set you can run the following query. 

SELECT @@global.time_zone

If the return value is +00:00/UTC  you have been successful, if it returns SYSTEM, you did something wrong, try it again. Last but not the least don't forget to insert your data in the standard MySQL date format "Y-m-d H:i:s" or use STR_TO_DATE function to let MySQL know what is what.

STR_TO_DATE(?, "%d %m %Y")

If you want to know more about good practices for applications with multiple timezones, read this post.

Have fun, keep hustling and never give up!