ASPit - Totally ASP JSit - Totally JavaScript
Search PHPit

Use this textbox to search for articles on PHPit. Seperate keywords with a space.


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 ('');
$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'])) . '';

(View Live Demo)

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.


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.

« Previous: GMT & Storing the timestamp

9 Responses to “Handling dates and times in PHP & MySQL”

  1. DG Says:

    The putenv functionality is very nice:

    // 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?

  2. DG Says:

    It’s not working in PHP 4.4.1 / Apache 1.3.xx / Windows.
    It works in PHP 5 / Apache 2 / Windows

  3. WoOzY Says:

    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

  4. Pejalo Says:

    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.

  5. John Josef Says:

    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.

  6. LoCo Says:

    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.

  7. Dennis Pallett Says:

    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.

  8. Rawb Says:

    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.

  9. Tony Rabun Says:

    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

Leave a Reply

About the author
Dennis Pallett is the main contributor to PHPit. He owns several websites, including ASPit and Chill2Music. He is currently still studying.
Article Index
  1. The Problem
  2. GMT & Storing the timestamp
  3. Wrapping it all up
Bookmark Article
Download Article
Download this article as a PDF file