Set Same Timezone In PHP, MySQL, and Apache
It provides the configurations required to set the same Timezone in PHP, MySQL, and Apache Web Server
We might be required to synchronize the Timezone followed by PHP, MySQL, and Apache Web Server to follow the same clock. This tutorial provides the configuration required to set the same timezone in PHP, MySQL, and Apache.
Notes: Make sure that proper testing is done before moving the application to production.
System Timezone - Ubuntu
We can obtain the Timezone on Ubuntu using the command timedatectl as shown below.
# timedatectl -> Ubuntu 18.04 LTS
timedatectl
# Output
Local time: Tue 2020-04-21 07:18:18 UTC
Universal time: Tue 2020-04-21 07:18:18 UTC
RTC time: Tue 2020-04-21 07:18:19
Time zone: Etc/UTC (UTC, +0000)
System clock synchronized: yes
systemd-timesyncd.service active: yes
RTC in local TZ: no
It shows the Local time and the Time zone. We can further refine it as shown below.
# timedatectl -> Ubuntu 18.04 LTS
timedatectl status | grep "Time zone"
# Output
Time zone: Etc/UTC (UTC, +0000)
System Timezone - Mac
We can obtain the Timezone on the Mac system using the terminal as shown below.
# Timezone on Mac
sudo systemsetup -gettimezone
# Output
Time Zone: US/Pacific
System Timezone - Windows
We can obtain the Timezone on the Windows PC using the command line as shown below.
# Timezone on Windows
systeminfo | findstr /C:"Time Zone"
# Output
Time Zone: (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi
PHP Timezone
The previous sections provided the steps to obtain system timezone on Ubuntu, Mac, and Windows. With this information, we can either set the PHP timezone to the same as that of the system, else it may differ from the system based on project requirements.
We can configure the Timezone in PHP either by updating the php.ini file which applies the same timezone for all the projects or by calling the method date_default_timezone_set for individual project or script.
Update php.ini File
Locate your php.ini file. I have provided a few examples as shown below.
# WampServer -> Example -> <WampServer Path>/bin/php/php7.2.14/php.ini
# XAMPP -> Example -> <XAMPP Path>/php/php.ini
# Ubuntu -> Apache Example -> /etc/php/7.2/apache2/php.ini
Now search your php.ini file for Timezone and update it as shown below.
[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
;date.timezone =
Uncomment the date.timezone to configure the timezone in PHP. The timezone value could be UTC, Asia/Kolkata, America/Los_Angeles, Pacific/Auckland, etc based on your exact requirements. The complete list of Timezones supported by PHP can be found at the Official Website.
[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = UTC
Now save the file after configuring the appropriate Timezone. You might be required to restart WampServer, XAMPP, Apache Web Server, NGINX based on your PHP usage.
Use the method date_default_timezone_set
We can also change the Timezone in PHP on the fly by calling the method date_default_timezone_set as shown below in the time conversion example.
<?php
// Globally define the Timezone
define( 'TIMEZONE', 'UTC' );
// Set Timezone
date_default_timezone_set( TIMEZONE );
// Set time
$time = new DateTime( '2020-04-21 05:10:15' );
echo $time->format( 'Y-m-d H:i:s' ); // 2020-04-21 05:10:15
// Get Timezone - Los Angeles
$laTimezone = new DateTimeZone( 'America/Los_Angeles' );
$time->setTimeZone( $laTimezone );
// Convert UTC to Los Angeles
echo $time->format( 'Y-m-d H:i:s' ); // 2020-04-20 22:10:15
MySQL Timezone
In this section, we will change the timezone in MySQL. It can also be done in several ways. We can either programmatically pass the offset according to the PHP timezone value or by updating the MySQL server timezone.
PHP Program (Preferred for PHP and MySQL)
We can programmatically set the MySQL timezone while initializing the DB session. This will ensure that the MySQL saves the datetime values according to the timezone passed by PHP. Also, note that the session time zone setting in MySQL affects zone-sensitive display and storage of time values. MySQL internally converts the session timezone values to the global timezone values while saving and retrieving them. This is the preferred way since PHP controls the timezone for both PHP and MySQL.
The below-mentioned example shows the usage of UTC offset to save and retrieve the Los Angeles timezone. If the MySQL global timezone is set to UTC, it internally converts the Los Angeles values to UTC while saving the values and returns back the values in Los Angeles while retrieving them. This will be useful for the applications supporting the timezone feature and works according to the user locale.
Notes:
- We can also use the named time zone instead of offset if the time zone information tables in the MySQL database have been created and populated. This will save us from calculating the offset. We can simply pass the timezone.
- The values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.
- The session time zone setting does not affect values in DATE, TIME, or DATETIME columns. In case it's required to store the values in UTC timezone to DATE, TIME, or DATETIME, an additional conversion from the application timezone to UTC timezone is required while saving the values to DATE, TIME, or DATETIME. This additional conversion ensures that the database values are stored in UTC and the same can be used to convert back to the required timezone.
<?php
// Define Timezone globally
define( 'TIMEZONE', 'America/Los_Angeles' );
// Set PHP Timezone
date_default_timezone_set( TIMEZONE );
// Obtain the Offset
$time = new DateTime();
$minutes = $time->getOffset() / 60;
$sign = ( $minutes < 0 ? -1 : 1 );
$minutes = abs( $minutes );
$hours = floor( $minutes / 60 );
$minutes -= $hours * 60;
$offset = sprintf( '%+d:%02d', $hours * $sign, $minutes );
// Set the Connection Timezone
$hostname = 'localhost';
$dbname = 'mydb';
$username = 'dbuser';
$password = 'dbpassword';
try {
$conn = new PDO( "mysql:host=$hostname;dbname=$dbname", $username, $password );
// Set DB Timezone using the Offset
$conn->exec( "SET time_zone='$offset';" );
echo "Connected successfully";
}
catch( PDOException $e ) {
echo "Failed to connect: " . $e->getMessage();
}
// Perform database operations
// Close the connection
$conn = null;
Instead of manually calculating the Offset, we can also use the format function as shown below.
<?php
// Define Timezone globally
define( 'TIMEZONE', 'America/Los_Angeles' );
// Set PHP Timezone
date_default_timezone_set( TIMEZONE );
// Obtain the Offset
$time = new DateTime();
$offset = $time->format( 'P' );
// Set the Connection Timezone
$hostname = 'localhost';
$dbname = 'mydb';
$username = 'dbuser';
$password = 'dbpassword';
try {
$conn = new PDO( "mysql:host=$hostname;dbname=$dbname", $username, $password );
// Set DB Timezone using the Offset
$conn->exec( "SET time_zone='$offset';" );
echo "Connected successfully";
}
catch( PDOException $e ) {
echo "Failed to connect: " . $e->getMessage();
}
// Perform database operations
// Close the connection
$conn = null;
MySQL Query
We can configure the timezone in MySQL by executing the query as shown below. The only issue is that the MySQL takes back its original value on restart.
# Query using Offset
SET GLOBAL time_zone = '+00:00';
# Query using name value - works only if time zone information tables are available
SET GLOBAL time_zone = 'America/Los_Angeles'
MySQL Server Configuration
Instead of configuring the timezone at the session level, we can also configure it at the global level which applies to all the databases hosted by the MySQL server. Also, make sure that it's configured only once while installing the database. MySQL does not update the existing values if we change it after the data accumulates. We must write appropriate jobs to convert the existing values to the new timezone before applying it at the global level.
We can update the my.ini file to preserve the MySQL timezone value at the global level. I have provided some of the standard locations to find the my.ini file as shown below.
# WampServer -> MySQL Example -> <Path to WampServer>/bin/mysql/mysql5.7.24/my.ini
# WampServer -> MariaDB Example -> <Path to WampServer>/bin/mariadb/mariadb10.3.12/my.ini
# XAMPP -> MySQL Example -> <Path to XAMPP>/mysql/bin/my.ini
# Windows -> MySQL Example -> C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
# Ubuntu -> MySQL Example -> /etc/mysql/my.cnf
Now update the timezone in MySQL at the global level as shown below.
# Timezone
default-time-zone = "America/Los_Angeles"
Save the file and restart MySQL to apply the changes.
Apache Timezone
We can also change the Apache Web Server timezone. It only impacts the CGI scripts. We need to locate the httpd.conf file to change the Apache timezone. I have provided some of the standard locations to find the configuration file as shown below.
# WampServer -> Example -> <Path to WampServer>/bin/apache/apache2.4.37/conf/httpd.conf
# XAMPP -> Example -> <Path to XAMPP>/apache/conf/httpd.conf
# Windows -> Example -> <Path to Apache>/conf/httpd.conf
Now update the timezone in Apache as shown below.
# Timezone in Apache - Affects only CGI Scripts
SetEnv TZ America/Los_Angeles
We can even set the Timezone in Apache by simply updating the .htaccess file. This will update the timezone for a specific project.
We might be required to change the Timezone of the Apache Web Server logs to be in sync with PHP and MySQL. The Apache Web Server uses the system Timezone by default while generating the logs. We can export the Apache Logs to the Elasticsearch using Logbeats by adjusting the time difference.
Summary
This tutorial provided the options to get the system timezone on Ubuntu, Windows, and Mac. It also provided the options to configure the timezone in PHP, MySQL, and Apache Web Server.
