Welcome to the third and last part of the ultimate PHP Time Handling guide.
This post focuses on how to handle time zones and daylight-saving time changes.
(Be sure to read part 1 and part 2 first, or you will miss some important points).
As a web developer, sooner or later you will need to deal with time zones. This is your chance to master this topic, so keep reading and feel free to ask any questions in the comments.
By reading this post you will learn:
- how time zones work in PHP;
- how to use the date_default_timezone_set() function;
- how to deal with daylight-saving time changes;
- how to include leap seconds in time interval calculations across different time zones;
- bonus: how to keep MySQL connections in sync with PHP time zone changes.
HOW TIME ZONES WORK IN PHP
In the second part of this guide we introduced the concept of time zone and we saw how a PHP script time zone can be set with date_default_timezone_set().
We also talked about Unix Time, a numeric implementation of UTC used by PHP, and we underlined the relationship between Unix Time and time zones showing how the same Unix Time value (which represent a precise moment in time) translates to a different datetime depending on the time zone we consider.
But what are time zones, exactly?
A time zone is basically a set of rules that translates the standard UTC time into a local time.
These rules usually include a base offset (that is, how many hours the local time is ahead or behind UTC) and a daylight-saving time period when the base offset is increased (or decreased), usually by 1 hour.
Daylight-saving time rules are set by governments and can be adjusted over time, so different years in the same time zone can follow different rules (especially for daylight-saving time periods).
For example, my local time zone (I live in Italy) has the following rules:
- the standard time is 1 hour ahead of UTC (the time zone is therefore UTC+1);
- the daylight-saving time period (since it was adjusted for the last time in 1996) starts at 2 A.M. of the last Sunday of March and ends at 3 A.M. of the last Sunday of October; during this period, the offset from UTC is increased to 2 hours (during this period the time zone becomes UTC+2).
Every place in the world has its own time zone, and UTC itself too can be considered a special time zone where the base offset is 0 and no daylight-saving time changes ever occur.
We actually used the “UTC time zone” in part 2, with the statement date_default_timezone_set(‘UTC’);
It’s important to keep in mind that functions like mktime() and date() are affected by time zone changes.
If your web application uses time related functions, you should be aware that changing the time zone will affect your application behaviour. Choosing the correct time zone to use is therefore essential to make the application work as you expect.
(Note: if you have problems using time zone names with date_default_timezone_set(), you may need to download the MySQL time zones table from here and import it into your database).
UTC vs LOCAL TIME
By default, PHP works in the same time zone of the computer it runs on.
You can choose to keep working in that time zone or switch to UTC (or even switch to a different time zone).
But which is the right choice?
It depends on which time related operations your application needs to perform.
If you just need to print a datetime (or a list of datetimes, for example when printing a list of events) then it’s perfectly fine to just keep working in local time, because the application audience expects the datetimes to be in local time.
However, if you need to analyse and store time related data (for example, calculate statistical measures and store them on a database) then it’s usually better to work in UTC in order to avoid local time ambiguities like the ones caused by daylight-saving time changes (as you will see in the next chapter).
The most complex case is the one where you need to perform time zone conversions. These situations require your application to switch to different time zones, maybe even inside a single script.
We will see a practical example in the last part of this guide. But first, we need to understand how daylight-saving time rules can affect time operations.
DAYLIGHT-SAVING TIME
One of the main problems of working in local time (that is, in a different time zone than UTC) is that daylight-saving time rules can create ambiguities.
Look at the following example:
<?php
date_default_timezone_set('Europe/Rome');
$year = 2017;
$month = 3; // March
$day = 26; // Last Sunday
$hour = 1; // 1 A.M.
$minute = 59;
$second = 58;
// Find the Unix Time of 2017-03-26 01:59:58 in the Europe/Rome time zone
$ut = mktime($hour, $minute, $second, $month, $day, $year);
for ($i = 0; $i < 5; $i++)
{
echo date('Y-m-d H:i:s', $ut + $i) . '<br>';
}
/* Output:
2017-03-26 01:59:58
2017-03-26 01:59:59
2017-03-26 03:00:00
2017-03-26 03:00:01
2017-03-26 03:00:02
*/
What happens in this example?
The script is working in the Italian time zone. Because of daylight-saving time rules, the datetime goes directly from 01:59:59 A.M. to 03:00:00 A.M.. All the times in between (so, the entire 2 A.M. hour) do not exist in that time zone.
If we try to get a Unix Time of a non-existing hour (in this case, 2 A.M.), mktime() parses that hour as the next hour from the previous one (the hour next to 1 A.M., in this case 3 A.M.).
This is correct. However, since in this case 2 A.M. is the same as the 3 A.M., the same Unix Time will be returned by mktime() for 3 A.M. too, creating a problematic situation.
This is clearly visible in the following example:
<?php
date_default_timezone_set('Europe/Rome');
$year = 2017;
$month = 3; // March
$day = 26; // Last Sunday
$hour = 1; // 1 A.M.
$minute = 30;
$second = 20;
$ut = mktime($hour, $minute, $second, $month, $day, $year);
echo 'Unix Time is: ' .strval($ut) . ', datetime is: ' . date('Y-m-d H:i:s', $ut) . '<br>';
$hour = 2; // 2 A.M. (non-existing hour)
$ut = mktime($hour, $minute, $second, $month, $day, $year);
echo 'Unix Time is: ' .strval($ut) . ', datetime is: ' . date('Y-m-d H:i:s', $ut) . '<br>';
$hour = 2; // 3 A.M.
$ut = mktime($hour, $minute, $second, $month, $day, $year);
echo 'Unix Time is: ' .strval($ut) . ', datetime is: ' . date('Y-m-d H:i:s', $ut) . '<br>';
/* Output:
Unix Time is: 1490488220, datetime is: 2017-03-26 01:30:20
Unix Time is: 1490491820, datetime is: 2017-03-26 03:30:20
Unix Time is: 1490491820, datetime is: 2017-03-26 03:30:20
*/
The opposite problem occurs when the daylight-saving time ends. In this case the same hour is repeated twice, leading to an even bigger problem. Look at this example:
date_default_timezone_set('Europe/Rome');
/* At 2 A.M. the daylight-saving time period ends, and the 2 A.M. hour is repeated twice */
echo mktime(1, 59, 59, 10, 29, 2017) . '<br>';
echo mktime(2, 0, 0, 10, 29, 2017);
/* Output:
1509235199
1509238800
*/
At 2 A.M. of the last Sunday of October the daylight-saving time period ends, and the whole 2 A.M. hour is repeated twice.
If we try to retrieve the Unix Time of the repeated hour, mktime() doesn’t know if we are referring to the first iteration or the second one.
In these cases, mktime() always returns the Unix Time of the second iteration. That means that the first hour is somehow “hidden”, and its Unix Time cannot be retrieved easily.
This explains why in the previous example, even if the second datetime is only 1 second after the first, the returned Unix Time is actually 3601 seconds after the first.
As you can see, working in local time can create a few problems. Working in UTC frees you from worrying about all these issues, so it’s usually a good idea do to that when possible.
MySQL TIME ZONE
SQL databases can store many different data types. Some SQL types are made specifically for dates and times, and some of them are affected by time zones too.
Let’s see how MySQL deals with time zones.
Just like a PHP script has its default time zone, a MySQL connection uses a default time zone too. As you may expect, the default MySQL time zone is usually the one of the server it runs on (unless it has been configured otherwise).
MySQL stores datetime fields in UTC and automatically converts them to the connection time zone when providing query results. We can change the connection time zone to get the datetime in whatever time zone we want.
Most of the time you want the MySQL time zone to be the same as PHP’s.
This is what would happen if the two time zones are NOT the same:
<?php
/* Database PDO connection. */
$db = NULL;
try {
$db = new PDO('mysql:host=localhost;dbname=test', '', '');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch (PDOException $e)
{
echo $e->getMessage();
die();
}
/* Set PHP time zone to UTC */
date_default_timezone_set('UTC');
/* Print the current UTC datetime */
echo date('Y-m-d H:i:s') . '<br>';
/* Now we print the current datetime taken from MySQL */
$st = $db->prepare('SELECT NOW()');
$st->execute();
$res = $st->fetch();
/* It's still in local time! */
echo $res[0] . '<br>';
/* Now we set the MySQL connection to UTC too */
$tz_query = 'SET time_zone = ?';
$st = $db->prepare($tz_query);
$st->execute(array('UTC'));
/* Print the MySQL current datetime again */
$st = $db->prepare('SELECT NOW()');
$st->execute();
$res = $st->fetch();
/* Now the MySQL time too is in UTC */
echo $res[0] . '<br>';
/* Output:
2018-04-30 19:44:16
2018-04-30 21:44:16
2018-04-30 19:44:16
*/
Be sure to keep your database safe! Read my SQL injection prevention guide.
The first output is the datetime returned by PHP, while the second and third outputs are from the MySQL server.
The MySQL function NOW() returns the current datetime. The first time the script executes it, the returned datetime is still in the default MySQL time zone and it’s different from the PHP one.
The MySQL time zone can be set with the query SET time_zone = ‘time zone’, as you can see in the previous example.
After the MySQL time zone has been set to be the same as PHP, the result from NOW() is the same as the one from the PHP date() function.
SWITCH BETWEEN MULTIPLE TIME ZONES
We are almost at the end of this guide.
In this final chapter you will find an example exercise with all the most important topics we covered so far: PHP time functions, Unix Time, time zones and leap seconds.
In this exercise we want to calculate a flight arrival time. We know the departure time and the flight length, and of course the departure and arrival time zones.
We also need to check for leap seconds and include them as well (and yes, I choose the date so that there is one).
You shouldn’t have any problem understanding the code, so I leave it to you 😉
Here it is:
<?php
$dept_timezone = 'Europe/Rome'; /* Departure time zone */
$arv_timezone = 'Europe/Moscow'; /* Arrival time zone */
/* Departure datetime */
$year = 2015;
$month = 7;
$day = 1;
$hour = 1;
$minute = 15;
$second = 1;
/* Flight length */
$flight_hours = 3;
$flight_minutes = 12;
$flight_seconds = 34;
date_default_timezone_set($dept_timezone); /* Set the departure time zone */
$dept_ut = mktime($hour, $minute, $second, $month, $day, $year); /* Find the departure Unix Time */
/* Find the arrival Unix Time, without considering leap seconds (for now) */
$arv_ut = $dept_ut + ($flight_hours * 3600) + ($flight_minutes * 60) + $flight_seconds;
date_default_timezone_set($arv_timezone); /* Set the arrival time zone */
/* Now we check if the offset between TAI and UTC has changed during the flight */
$dept_tai_offset = tai_offset($dept_ut);
$arv_tai_offset = tai_offset($arv_ut);
/* And then we subtract the difference */
$arv_ut -= ($arv_tai_offset - $dept_tai_offset );
/* Finally, we print the arrival datetime */
echo date('Y-m-d H:i:s', $arv_ut);
/* Output: 2015-07-01 05:27:34 */
/* Functions (same as in part 2) */
/* Calculates the offset between UTC and TAI, including leap seconds */
function tai_offset($unix_t)
{
/* Array containing offset changes */
$offset = offset_array();
/* Offset between UTC and TAI (10 seconds is the base offset) */
$ts_offset = 10;
/* We iterate the offset array until we arrive at our datetime, adding leap seconds as needed */
foreach ($offset as $key => $value)
{
/* $key here contains the Unix Time of the leap second datetime */
if ($key <= $unix_t)
{
$ts_offset = $value;
}
}
return $ts_offset;
}
/* This function creates an array of offsets between UTC and TAI.
It gets the leap seconds information from the IETF file */
function offset_array()
{
/* This is the number of seconds from January 1st 1900 to 1970 (UTC). This is needed for parsing the IETF file */
$base_t = '2208988800';
/* Result array */
$offset = array();
/* Read the IETF file (note: here, for the sake of simplicity, we assume no errors occur) */
$file = file('https://www.ietf.org/timezones/data/leap-seconds.list', FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
/* Read the file lines */
foreach ($file as $line)
{
/* Check whether the line starts with a digit, meaning it actually contains offset information instead of comments */
if (ctype_digit(mb_substr($line, 0, 1)))
{
/* Each file value is separated by a tab */
$line_array = explode("\t", $line);
/* The offset, in seconds, starts from January 1st 1900 so we need to subtract $base_t to get the Unix Time.
Note that numbers here can exceed the 32 bits integer's size, so we need to use the BC Math extension. */
$offset_t = bcsub($line_array[0], $base_t);
$offset_t = intval($offset_t, 10);
/* Set the result array key => value, using the Unix Time as key and the offset as value */
$offset[$offset_t] = intval($line_array[1], 10);
}
}
return $offset;
}
If you have any questions, feel free to ask in the comments below or on my Facebook Group: Alex PHP café.
If this guide has been helpful to you, please share it using the buttons below… thanks!
Alex