Handling dates and times in PHP & MySQL
(Page 1 out of 3)Abstract
This article will show you how to use dates and times in PHP & MySQL, by taking a look at the problems that arise when handling dates and times, and how to solve them
Introduction
Whenever you create a PHP script, there's a big chance it'll involve handling dates and times, when for example you need to create a content management system or an event tracker. Handling dates and times in your scripts doesn't sound so hard, after all, it's just a few numbers to track, but in reality this is actually much harder than you think. In fact, using dates and times can be one of the most challenging tasks to do, especially if you want to do it properly.
That's why in this article I'm going to take you through the necessary steps to properly handle dates and times. First we'll have a look at the problem, and after that we'll go through the steps necessary to solve it.
The problem: handling dates and times
It seems really easy, to use dates and time, and most of us tend to take the easy road, and simply store a timestamp in the database. It's not uncommon to see something like this:
// Get item variables
// ...
// Generate timestamp
$item['timestamp'] = time();
// Insert item
$db->insert_item ($item);
?>
This is probably the way most of us do it, and in fact, that's how I usually do it as well, because there's no hassle at all.
But there are several problems with this method. The biggest problem is that the timezone of this date isn't taken in account. If you don't know what a timezone is, it's a zone where a certain time is set, and there are different timezones all over the world. For example, I live in the GMT+1 timezone, which means it could be 10 PM here, and 1 AM in the US.
Getting back to the problem, the timestamp stored in the database is based on the timezone of the server, but what if we move to a different server with a different timezone? Or what if we want to display the date in the visitor's own timezone? That would be impossible, since we don't know the original timezone.
To solve the problem we need to store the timezone along with the timestamp. To do that, we must use the base timezone called GMT as the reference, and account for the number of hours our timezone is different from the GMT timezone. The easiest way to do that is to get the offset with the date() function and using the 'Z' argument as format, see the example below:
// Get GMT timestamp
$gm_time = time() - date('Z', time());
?>
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