Handling dates and times in PHP & MySQL
(Page 2 out of 3)Using the GMT timestamp
With the GMT timestamp and the timestamp of our own timezone, it's possible to know how many hours our own timezone is off (when compared to GMT), and this also means that it's now possible to display the timestamp in different timezones.
The example below demonstrates the use of the GMT timestamp:
// Get timestamp in server timezone
$time = time();
echo "Server time: " . date("d/m/Y H:i", $time) . "";
// Get GMT timestamp
$gm_time = $time - date('Z', $time);
echo "GMT time: " . date("d/m/Y H:i", $gm_time) . "";
// Difference:
$diff = ($time - $gm_time)/3600;
if ($diff < 0) {
$timezone = 'GMT' . $diff;
} else {
$timezone = 'GMT+' . $diff;
}
echo 'Server timezone: ' . date('T', $time) . '';
echo 'Server timezone offset: ' . $timezone;
?>
Just to demonstrate that it's now possible to display the timestamp in a different timezone, see the below demo, which shows the current time in four different time zones.
$original_tz = getenv('TZ');
$format = "d/m/Y H:i";
// Server time:
$server_time = time();
echo 'Server time: ' . date($format, time()) . '';
// GMT time:
$gm_time = $server_time - date('Z', $server_time);
echo "GMT time: " . date($format, $gm_time) . "";
// Africa/Cairo
putenv('TZ=Africa/Cairo');
$a_time = $gm_time + date('Z', time());
putenv('TZ=' . $original_tz);
echo 'Africa/Cairo time: ' . date($format, $a_time) . "";
// America/Los Angeles
putenv('TZ=America/Los_Angeles');
$a_time = $gm_time + date('Z', time());
putenv('TZ=' . $original_tz);
echo 'America/Los Angeles: ' . date($format, $a_time) . "";
// Europe/London
putenv('TZ=Europe/London');
$a_time = $gm_time + date('Z', time());
putenv('TZ=' . $original_tz);
echo 'Europe/London: ' . date($format, $a_time) . "";
putenv('TZ=' . $original_tz);
?>
(PHP5 only)
The above example temporarily changes the time zone of the server to the location we want, then gets the offset in hours, and then reset the time zone. The offset is then used to calculate the time.
In reality, you would never change the timezone to get the timestamp, but instead supply the offset (for example the user can select his/her timezone) or automatically detect it using some JavaScript.
Now that we know what to store, we have to take a look at how we store it, because MySQL also comes with inbuilt date handling.
Storing the timestamps
The easiest way to store the timestamps is to store them as integers, and that's the usual route developers take, but it's not the best one.
If you're not planning on using the timestamps in your SQL queries, it's no problem to store them as integers, because you won't be using any of MySQL's inbuilt date functions anyway. But if you are planning to use them in your queries, you might want to reconsider.
When using timestamps in your queries, you'll have to let MySQL convert them to its own format, using the FROM_UNIXTIME function, and only then can you use the other inbuilt functions, like DAY, MONTH, etc. Let's say you want all items added in January. A SQL query to do this would look something like this:
But the problem comes from using the FROM_UNIXTIME function. It comes with inbuilt support for time zones, which means it won't properly convert your timestamps, at least not the way you expect them to be converted.
This means you will have to store timestamps in MySQL's own date format, which looks like this: YYYY-MM-DD HH:MM:SS, and then when you retrieve the records you will have to convert it back into a timestamp (and ALWAYS work with timestamps!).
By storing the timestamps in MySQL's own date format, you won't have to use the FROM_UNIXTIME() function, which means MySQL won't change your timestamps, so that's one less thing to worry about. And you can still use all the other MySQL date functions.
The following three functions can be used to convert timestamps into MySQL dates and vice versa:
function getMysqlDatetime($timestamp){return date("Y-m-d H:i:s", $timestamp);}
function getTimestamp($mysql_datetime){return strtotime($mysql_datetime);}
(Made by Keith Devens, http://keithdevens.com)
Before you insert any timestamp, make sure to use the above functions, like so:
$db->execute ("INSERT INTO items (title, timestamp, gmt_timestamp) VALUES ('My Item', '" . getMySqlDatetime($timestamp) . ", '" . getMysqlDatetime($gmt_timestamp) . "')";
?>
April 10th, 2006 at 5:21 pm
The putenv functionality is very nice:
// Europe/London
putenv(’TZ=Europe/London’);
$a_time = $gm_time + date(’Z', time());
putenv(’TZ=’ . $original_tz);
echo ‘Europe/London: ‘ . date($format, $a_time) . “”;
But what can we do when working with PHP4?
April 10th, 2006 at 5:29 pm
Clarification:
It’s not working in PHP 4.4.1 / Apache 1.3.xx / Windows.
It works in PHP 5 / Apache 2 / Windows
April 10th, 2006 at 6:32 pm
But yeah, what if I wanna show user friendly relative time? I usually do like this:
function relative_date($date)
{
global $lang;
$sec = time() - $date;
switch(true) {
case $sec
April 11th, 2006 at 12:28 am
from page 1: “the timestamp stored in the database is based on the timezone of the server”
Is this accurate? For me, time() is returning the same value on servers located within different timezones. I only worry about timezones when I DISPLAY a time, in which I use gmdate() and add/subtract the seconds of time difference depending on the timezone I want the time to be formatted for.
April 11th, 2006 at 2:59 am
When storing information you should store raw data. Upon retrieving that data you should parse it (using the date() function). This information is a possibly hazardous practice when handling data because you are altering the timestamp information local to your host machine.
April 12th, 2006 at 2:47 am
Recently I started to use this :)
I save date as int - YYYYMMDD… :)
and than use sql substring function i retreive what i need
I beleive that it is not good aproach when dealing with large database, but in this case it was pretty usefull and quick solution.
April 12th, 2006 at 9:03 pm
DG: I’m afraid it doesn’t work for PHP4 at all. I tried a couple of different things myself, but couldn’t get it working properly.
Pejalo: Are you sure about that? The time() on the PHPit server is quite different from my own localhost.
John: I’m actually saying you should store the original timestamp, and the gmt_timestamp. This allows the time to be formatted in the visitor’s own timezone. Maybe you’re talking about forms and filling in dates?
LoCo: By storing the date this way, you probably won’t be able to use MySQL’s inbuilt date functions (like DAY(), MONTH()) etc. This makes a lot harder to do date-based SQL queries.
April 13th, 2006 at 12:20 am
Please, whatever you do, do not follow this person’s advice.
I am sure they had the best intentions in mind, but taking a project down this path is a surefire path into madness.
I have written code the exact way he suggested for many years, and in the end, it only produced pain. During daylight savings changes, all sorts of crazy things happen, because yesterday’s date(”Z”) is not equal to today’s. So when something happened yesterday at 3:30 PM, today it shows it as happening as 2:30 PM because of the hour difference.
You can store your data in your database as either a unix timestamp (recommended, because it is ALWAYS stored in GMT), or as a GMT timezone date string. When you pull it out, you will probably want to convert the date string into a timestamp, so be sure to use the gmmktime because you are feeding it a GMT timestamp.
From there on out, either use the putenv(”TZ=some/timezone”); or in PHP 5.1 and newer: date_default_timezone_set(”some/timezone”); and then just happily use the date(), mktime() and strtotime() functions as they were designed to be used.
Many man hours have been poured into various standard time handling libraries so that date() will always produce the correct date given the timezone and timestamp. Do not attempt to reimplement all of their work in a cheezy little library, you won’t get it right, I promise. Again, I repeat, do not attempt to reproduce the pre-existing time handling functions, you will fail.
Please listen to my advice, it will save you hours and hours of pain sometime in the future.
May 31st, 2006 at 7:53 pm
I agree that rewriting these libraries makes no sense, then again they get rewritten in every implementation of PHP…an indicator that no one likes using what is there. perhaps some object oriented code is due along the lines of .NET or Java? meanwhile putenv actually marshals data across process boundaries to get to the environment variables of the process in which your *webserver* is running. why would you do that just to format a date or time? it will slow your machine down and make your code dependent on local machine settings that you are changing for each and every script. in PHP5 use date_default_timezone_set to set the timezone for the current script instead of the whole machine. this ensures that your application does not hose up anything else and that you are not using resources marshalling across process boundaries