Best practise storing time

Posted on 2012-08-29
Last Modified: 2012-09-16
Cannot get my head around the best way to do this.
I have multiple clients in different time zones
I currently store date/times in UTC and format the sql with what the real time is for the client based on their timezone at the moment they make a request.
Problem with this is when the timezone changes (i.e. Summer time/Winter time) this won't work if the client requests data for the previous day then I would be calculating the date/time of the record based on the new time which could be out by 1 hour.
So what do people suggest is the best method for saving the date/time?
Put the actual time based on their timezone in the db and also use UTC for a base reference?
Or something else....?
Question by:Gary

    Assisted Solution

    I work for a company that service clients globally and we have looked at several approaches to this. There is only 2 main options:

    Option 1. Manually store and manually convert the timestamps-
    Store in Database as UTC Time
    Convert using $sourceutctime="2012-08-29 10:30:00"; // Source UTC Time $usertz="10.00"; //+10:00 (Australian Eastern Standard Time) $userdst="1.00"; //+1 hour for Daylight Savings $convertedtime=date("Y-m-d H:i:s",strtotime($sourceutctime + (60*60*($usertz+$userdst)))) or for negative timezones $sourceutctime="2012-08-29 10:30:00"; // Source UTC Time $usertz="4.00"; $userdst="1.00"; $convertedtime=date("Y-m-d H:i:s",strtotime($sourceutctime - (60*60*($usertz+$userdst)))) Note the usage of + for adding to UTC time and - for subtracting from UTC time

    The only problem with the manual approach is the governments around the world keep changing when Daylight time starts and ends.

    Option 2. Use PHP to adjust the times
    Use the date_default_timezone_set function
    A List of supported timezones can be found HERE

    On the Supported timezone page you will notice theres a NOTE that says "The latest version of the timezone database can be installed via PECL's » timezonedb" this is how to keep php's timezonedb up to date.
    LVL 107

    Accepted Solution

    Please see this article.  It covers the issue and hopefully will give you some good ideas about how to handle timezones, summer times, etc.  I think you will be on firm ground if you store UNIX timestamp values (by definition UTC) in the data base and use timezones to express local times.
    LVL 58

    Author Comment

    Excellent article Ray.
    I ended up converting to their local time zone and storing that, makes it easier for future queries if I don't have to worry about what the real time should be.
    But your post has made me think, is there advantages to storing as a unix timestamp and then comparing on seconds rather than by dates.
    Say I had thousands of queries per hour - how would this stand up vs storing dates and running queries on the actual readable date and also the conversion back to a readable form in the php page itself
    LVL 107

    Expert Comment

    by:Ray Paseur
    GaryC123, thanks for your kind words.

    The program time to convert dates between string representations and integer representations has never been apparent to me -- that is to say, I have never seen it become any kind of performance issue.  I believe that MySQL may be storing the UNIX timestamp and just telling us the DATETIME values when we query the data base (not sure about that).

    There are 3600 seconds in an hour, so thousands of queries per hour is not much of a load ;-)
    LVL 58

    Author Comment

    I was just thinking doing a sql where date=> and date<= might be faster if I was just evaluating based on an int field containing seconds
    But do I lose any speed benefit then converting that timestamp to a human date...
    Will have to ponder this a while..
    LVL 107

    Assisted Solution

    by:Ray Paseur
    You do not lose any measurable amount of time converting.  But in the query you might want to use the MySQL BETWEEN keyword.  You can use EXPLAIN SELECT to get MySQL to show you exactly how it handles the WHERE clause.  IIRC, BETWEEN is a shorter path through the query than two conditional AND clauses.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now