For one of my projects I needed to change time zone depending on user location. Time zones had to be changed both in php and mysql. While for php everything was very simple, I had to do some preparation steps before I was able to change current time zone for mysql. So, goal of this artile to provide simple and brief explanation on how to configure and use time zones in php and mysql.
Everything is simple with php code:
Actually with mysql everything is simple too, but you will have to do some preparations before you will be able to change time zone by SQL statement. Basically here is a simple SQL statement that changes current time zone:
SET time_zone = 'Canada/Eastern'
The mysql_tzinfo_to_sql program is used to load the time zone tables. On the command line, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example:
shell> mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.
You can find more information on mysql time zone configuration in mysql time zone documentation.