Best practise storing time

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....?
LVL 58
GaryAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
 
steg2005Connect With a Mentor Commented:
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.
0
 
GaryAuthor Commented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
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 ;-)
0
 
GaryAuthor Commented:
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..
0
 
Ray PaseurConnect With a Mentor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.