Handling dates and times in PHP & MySQL
(Page 3 out of 3)Wrapping it all up in one neat class
Let's create one neat class which can handle most of the things I've discusses in this article. Our class needs to be able to:
- create a timestamp and a corresponding GMT timestamp, and give us the offset as well
- calculate the time in any other timezone, by adding or subtracting hours
- calculate the time in any other timezone, by specifying the timezone
- be able to convert into the MySQL date format and vice versa
Since the class is a bit too long to fully post here, you can click here to download it.
See the example below on how to use the class:
include ('class.date.php');
$d = New Date;
$format = "d/m/Y H:i";
// Get timestamp
$time = $d->getTimestamp();
echo ''
; print_r ($time); echo '';
// Convert to MySQL date format
$mysql_time = $d->toMysqlDatetime($time);
echo ''
; print_r ($mysql_time); echo '';
// Convert into another timezone
echo 'Europe/London: ' . date($format, $d->toTimezone($time['server_time'], 'Europe/London', $time['gmt_time'])) . '';
echo 'Africa/Cairo: ' . date($format, $d->toTimezone($time['server_time'], 'Africa/Cairo', $time['gmt_time'])) . '';
echo 'America/Los_Angeles: ' . date($format, $d->toTimezone($time['server_time'], 'America/Los_Angeles', $time['gmt_time'])) . '';
// Convert into another timezone (e.g. GMT+1, GMT+2, etc)
echo 'GMT+1: ' . date($format, $d->toGmtTimezone($time['server_time'], +1, $time['gmt_time'])) . '';
echo 'GMT+5: ' . date($format, $d->toGmtTimezone($time['server_time'], +5, $time['gmt_time'])) . '';
echo 'GMT-9: ' . date($format, $d->toGmtTimezone($time['server_time'], -9, $time['gmt_time'])) . '';
?>
Using the class you can handle almost any date/time problem, and you won't have to worry about using dates and times anymore, since this class handles most of the difficult things for you.
Conclusion
In this article I've shown you how to use and store dates and times in PHP and MySQL. This isn't the only way, but it is one I've had much success with. I can't take credit for it though, as I originally got the idea from Keith Devens. In his entry he pretty much explains exactly what I've explained in this article (except his entry is a bit shorter). I did write the Date class myself though.
If you have any suggestions or comments, feel free to drop 'em below, or join us at the PHPit Forums.
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