The problem
Date handling with PHP is a complex task, especially when application users are located in different timezones (all over the world) and they are using different date format (with one word, application needs internationalization).
The problem becomes much more complex, when you want your data to be portable between different databases. Most database engines (except SQLite) offer native data types for date or datetime. So, insert and retrieve dates is not a standardized procedure.
Many people, who develop only for MySQL, they are using MySQL timestamp. The date value stored by MySQL internally as UTC regardless of the server or user timezone. This way the data is portable regardless of your server’s specific configuration. It is a good solution for MySQL, but not for other RDBMS.
For many years, php date handling was synonym with the use of mktime(), date() and similar functions. Fortunately, since php 5.2 DateTime class exists. DateTime class can perfectly handle and convert different timezones, dateformats and deal with Daylight Savings Time changes.
The solution
To simplify this situation, I use (for many years) the following methodology, which I have to say, it is a little heretical approach, as it does not use date columns at all:
- Dates are stored as text in varchar(14) fields (not to date fields) in format yyyymmddhhnnss (any part of this format is filled with zeros, where data is unavailable)
- Dates are stored in UTC
- On application interface, UTC date is converted according to
user timezone and preferred dateformat. I use two simple php
functions to encode and decode dates (get the code at the end of
the post)
date_encode
Converts a date string of any format and any timezone to UTC timestamp (yyyymmddhhnnss)date_decode
Converts a UTC timestamp (yyyymmddhhnnss) to date string of any format and timezone (user preferences).
Notes
In order to make the date information portable between various databases, a similar approach would be to store the UNIX timestamp of each date (number of seconds since the epoch), but this has certain restrictions for date range and it is more complex.
Someone could say that storing dates as text might cause performance issues. According to our experience, this does not happen, except the table has more than 3-5 millions rows (with modern databases and hard disks such issues have been eliminated).
Why UTC
From the excellent Wikipedia article, I am mentioning:
Coordinated Universal Time (UTC) is the primary time standard by which the world regulates clocks and time. It is one of several closely related successors to Greenwich Mean Time (GMT). For most purposes, UTC is synonymous with GMT, but GMT is no longer precisely defined by the scientific community.
UTC (also called Zulu time) is the standard international time. All time zones are expressed as offsets of UTC. Using php DateTime class it is easy to compute the time in user timezone from UTC, and you don’t have to worry about for Daylight Savings Time changes.
How to detect User timezone
There are three ways:
- client-side, with Javascript (please note that javascript detection is not always accurate, so you cannot rely on this for critical operations). There is a sample code at the end of the post.
- using Geolocation API (it is an accurate method, please see here and here)
- obtain user timezone and preferred dateformat during user registration.
We prefer the third method, as it is strict and simple. In case of a public site (as most blogs, for example, where user registration is not required), dates are simply presented in UTC.
The code
Syntax highlight using http://alexgorbatchev.com/SyntaxHighlighter/
definitions
/* set server default timezone (it is possible to set from php.ini) */
define('CONST_SERVER_TIMEZONE', 'UTC');
date_default_timezone_set(CONST_SERVER_TIMEZONE);
/* set server dateformat */
define('CONST_SERVER_DATEFORMAT', 'YmdHis');
define('CONST_SAFE_DATEFORMAT_STRTOTIME', 'Y-m-d H:i:s');
date_encode
/**
* Converts a date string of given timezone (considering DST) and format to 14-digit UTC timestamp (YYYYMMDDHHMMSS)
*
* DateTime::createFromFormat requires PHP >= 5.3
*
* Note about strtotime: Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components:
* if the separator is a slash (/), then the American m/d/y is assumed;
* whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.
*
* To avoid potential ambiguity, it's best to use ISO 8601 (YYYY-MM-DD) dates or DateTime::createFromFormat() when possible.
*
* @param $str_user_datetime
*
* $str_user_timezone and $str_user_dateformat must match. Otherwise error occurs.
*
* If $str_server_dateformat is longer than $str_user_dateformat,
* the missing time digits filled with zero, but if all times digits are missing current time is returned.
*
* Other values (invalid datetime strings) throw an error. Milliseconds are not supported.
*
* @param $str_user_timezone
* @param $str_user_dateformat
* @param string $str_server_timezone
* @param string $str_server_dateformat
* @param string $str_safe_dateformat_strtotime
* @return string
*
* @link http://www.php.net/manual/en/function.strtotime.php
* @link http://stackoverflow.com/questions/4163641/php-using-strtotime-with-a-uk-date-format-dd-mm-yy
* @link http://derickrethans.nl/british-date-format-parsing.html
*/
function date_encode($str_user_datetime,
$str_user_timezone,
$str_user_dateformat,
$str_server_timezone = CONST_SERVER_TIMEZONE,
$str_server_dateformat = CONST_SERVER_DATEFORMAT,
$str_safe_dateformat_strtotime = CONST_SAFE_DATEFORMAT_STRTOTIME) {
// set timezone to user timezone
date_default_timezone_set($str_user_timezone);
// create date object using any given format
if($str_user_datetime == 'now' || !$str_user_datetime) {
$date = new DateTime('', new DateTimeZone($str_user_timezone));
} else {
$date = DateTime::createFromFormat($str_user_dateformat, $str_user_datetime, new DateTimeZone($str_user_timezone));
if($date === false) {
trigger_error('date_encode: Invalid date', E_USER_ERROR);
}
}
// convert given datetime to safe format for strtotime
$str_user_datetime = $date->format($str_safe_dateformat_strtotime);
// convert to UTC
$str_server_datetime = gmdate($str_server_dateformat, strtotime($str_user_datetime));
// return timezone to server default
date_default_timezone_set($str_server_timezone);
return $str_server_datetime;
}
date_decode
/**
* Converts a UTC timestamp to date string of given timezone (considering DST) and given dateformat
*
* DateTime requires PHP >= 5.2
*
* @param $str_server_datetime
*
* Normally is a 14-digit UTC timestamp (YYYYMMDDHHMMSS). It can also be 8-digit (date), 12-digit (datetime without seconds).
* If given dateformat ($str_user_dateformat) is longer than $str_server_datetime,
* the missing digits of input value are filled with zero,
* so (YYYYMMDD is equivalent to YYYYMMDD000000 and YYYYMMDDHHMM is equivalent to YYYYMMDDHHMM00).
*
* It can also be 'now', null or empty string. In this case returns the current time.
*
* Other values (invalid datetime strings) throw an error. Milliseconds are not supported.
*
* @param string $str_user_timezone
* @param $str_user_dateformat
* @return string
*/
function date_decode($str_server_datetime,
$str_user_timezone,
$str_user_dateformat) {
// create date object
try {
$date = new DateTime($str_server_datetime);
} catch(Exception $e) {
trigger_error('date_decode: Invalid datetime: ' . $e->getMessage(), E_USER_ERROR);
}
// convert to user timezone
$userTimeZone = new DateTimeZone($str_user_timezone);
$date->setTimeZone($userTimeZone);
// convert to user dateformat
$str_user_datetime = $date->format($str_user_dateformat);
return $str_user_datetime;
}
Detect user timezone with javascript
/**
* @see http://stackoverflow.com/questions/2853474/can-i-get-the-browser-time-zone-in-asp-net-or-do-i-have-to-rely-on-js-operations
* @return {String}
*/
function getTimezoneName() {
var tmSummer = new Date(Date.UTC(2005, 6, 30, 0, 0, 0, 0));
var so = -1 * tmSummer.getTimezoneOffset();
var tmWinter = new Date(Date.UTC(2005, 12, 30, 0, 0, 0, 0));
var wo = -1 * tmWinter.getTimezoneOffset();
if(-660 == so && -660 == wo) return 'Pacific/Midway';
if(-600 == so && -600 == wo) return 'Pacific/Tahiti';
if(-570 == so && -570 == wo) return 'Pacific/Marquesas';
if(-540 == so && -600 == wo) return 'America/Adak';
if(-540 == so && -540 == wo) return 'Pacific/Gambier';
if(-480 == so && -540 == wo) return 'US/Alaska';
if(-480 == so && -480 == wo) return 'Pacific/Pitcairn';
if(-420 == so && -480 == wo) return 'US/Pacific';
if(-420 == so && -420 == wo) return 'US/Arizona';
if(-360 == so && -420 == wo) return 'US/Mountain';
if(-360 == so && -360 == wo) return 'America/Guatemala';
if(-360 == so && -300 == wo) return 'Pacific/Easter';
if(-300 == so && -360 == wo) return 'US/Central';
if(-300 == so && -300 == wo) return 'America/Bogota';
if(-240 == so && -300 == wo) return 'US/Eastern';
if(-240 == so && -240 == wo) return 'America/Caracas';
if(-240 == so && -180 == wo) return 'America/Santiago';
if(-180 == so && -240 == wo) return 'Canada/Atlantic';
if(-180 == so && -180 == wo) return 'America/Montevideo';
if(-180 == so && -120 == wo) return 'America/Sao_Paulo';
if(-150 == so && -210 == wo) return 'America/St_Johns';
if(-120 == so && -180 == wo) return 'America/Godthab';
if(-120 == so && -120 == wo) return 'America/Noronha';
if(-60 == so && -60 == wo) return 'Atlantic/Cape_Verde';
if(0 == so && -60 == wo) return 'Atlantic/Azores';
if(0 == so && 0 == wo) return 'Africa/Casablanca';
if(60 == so && 0 == wo) return 'Europe/London';
if(60 == so && 60 == wo) return 'Africa/Algiers';
if(60 == so && 120 == wo) return 'Africa/Windhoek';
if(120 == so && 60 == wo) return 'Europe/Amsterdam';
if(120 == so && 120 == wo) return 'Africa/Harare';
if(180 == so && 120 == wo) return 'Europe/Athens';
if(180 == so && 180 == wo) return 'Africa/Nairobi';
if(240 == so && 180 == wo) return 'Europe/Moscow';
if(240 == so && 240 == wo) return 'Asia/Dubai';
if(270 == so && 210 == wo) return 'Asia/Tehran';
if(270 == so && 270 == wo) return 'Asia/Kabul';
if(300 == so && 240 == wo) return 'Asia/Baku';
if(300 == so && 300 == wo) return 'Asia/Karachi';
if(330 == so && 330 == wo) return 'Asia/Calcutta';
if(345 == so && 345 == wo) return 'Asia/Katmandu';
if(360 == so && 300 == wo) return 'Asia/Yekaterinburg';
if(360 == so && 360 == wo) return 'Asia/Colombo';
if(390 == so && 390 == wo) return 'Asia/Rangoon';
if(420 == so && 360 == wo) return 'Asia/Almaty';
if(420 == so && 420 == wo) return 'Asia/Bangkok';
if(480 == so && 420 == wo) return 'Asia/Krasnoyarsk';
if(480 == so && 480 == wo) return 'Australia/Perth';
if(540 == so && 480 == wo) return 'Asia/Irkutsk';
if(540 == so && 540 == wo) return 'Asia/Tokyo';
if(570 == so && 570 == wo) return 'Australia/Darwin';
if(570 == so && 630 == wo) return 'Australia/Adelaide';
if(600 == so && 540 == wo) return 'Asia/Yakutsk';
if(600 == so && 600 == wo) return 'Australia/Brisbane';
if(600 == so && 660 == wo) return 'Australia/Sydney';
if(630 == so && 660 == wo) return 'Australia/Lord_Howe';
if(660 == so && 600 == wo) return 'Asia/Vladivostok';
if(660 == so && 660 == wo) return 'Pacific/Guadalcanal';
if(690 == so && 690 == wo) return 'Pacific/Norfolk';
if(720 == so && 660 == wo) return 'Asia/Magadan';
if(720 == so && 720 == wo) return 'Pacific/Fiji';
if(720 == so && 780 == wo) return 'Pacific/Auckland';
if(765 == so && 825 == wo) return 'Pacific/Chatham';
if(780 == so && 780 == wo) return 'Pacific/Enderbury'
if(840 == so && 840 == wo) return 'Pacific/Kiritimati';
return 'US/Pacific';
}
Entrepreneur | Full-stack developer | Founder of MediSign Ltd. I have over 15 years of professional experience designing and developing web applications. I am also very experienced in managing (web) projects.
