• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

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....?
0
Gary
Asked:
Gary
  • 3
  • 2
3 Solutions
 
steg2005Commented:
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
 
Ray PaseurCommented:
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
 
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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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 PaseurCommented:
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now