From: David T. <tom...@us...> - 2011-09-20 08:35:09
|
Peter, good that you're looking at this kind of thing, because it is at the core of the application (and quite a few problems). Historically, I wanted to move away from the original design for time entries that was storing the start and end dates. It eases a lot of problems to store just the start date and a duration. But this is only half implemented, meaning we have three fields: start, end and duration. This was meant to be temporary, but we haven't got around to fixing it. My goal would still be to remove the end-dates. As to how we store start (and end) dates, I think you are right - it should be as neutral as possible, i.e. UTC. But then in order be precise, each time entry should store the user's current timezone too, so that we can precisely retrieve the original value. I did not realise that mySQL has a conversion too, we have to check the options (system, php and mysql timezones) to store the right type/value. One issue was to break up entries that crossed "midnight", this was to make querying time periods easier. But since "midnight" is relative to your timezone, I'm not sure that it will help in the long term (we have to analyse start-time and duration anyway). The comments about the bug in mySQL are older than my association with the project, I think we can happily correct this now (and remove the comments)... Cheers Date: Tue, 20 Sep 2011 15:45:07 +1000 From: pal...@gm... To: tsh...@li... Subject: [Tsheetx-developers] Timezones, time adjustment and UTC I have been wrestling with why some of the timestamps in my database are 14 or 15 hours out of whack. Specifically the end_time is wrong. If I clockin/out at 10:00-11:00 am, the timestamps in the db are start_time of 10am and end_time of 2am the next day. Duration shows as 60 mins. So I think what is happening is that function fixStartEndDuration and fix_entry_endstamp are changing the end times. There is this comment at the start of function fixStartEndDuration //Due to a bug in mysql with converting to unix timestamp from the string, //we are going to use php's strtotime to make the timestamp from the string. //the problem has something to do with timezones. Can anyone shed some light on what this bug might have been? Was it a mysql 4.x bug? The url http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php mentioned in the same function talks about handling timezones in php. It appears to me that this web site may have changed since the url was pasted into the function as I remember there were a few added comments then which are no longer there. So any description of what bug there was, is no longer there. This problem is most likely influenced by the system timezone, which mysql uses, and whatever timezone is given to php. But I haven't investigated that on my system yet. In a future release i would like to change the way dates/times are stored in the database. I think the database needs to keep time in UTC only. Now we could change the mysql datetime fields to timestamp. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. datetime fields store time in local time with no conversion i.e. set by the server's timezone. See here for a discussion http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ with some suggestions in this blog that datetime fields are better for performance, possibly because the database does not have to do date conversions. There is also a short howto /user experience on converting your database to using UTC: http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration Anyway, the reason I want to convert to UTC in the database is to remove the probable timezone problems that fixStartEndDuration seems to be trying to correct. The more important reason however is to support multiple timezones. Since we have made a major effort to internationalise the language in the web pages, we should also manage the timestamps better. Being able to manage multiple timezones would mean allowing each user to save and display his times in his own timezone. The application then becomes a useful application for international use, where users are in many different timezones. Peter |