Re: [Tsheetx-developers] Timezones, time adjustment and UTC

[David T. <tom...@us...>]

Storing the duration saves having to calculate it all the time from the start and end times, as you say we can then just query it via SQL. Most of this code is gone now (thanks to Scott I think). Keeping the end date just means having redundant data in the DB, i.e. if I change a time entry I have to ensure data integrity. But you are right, it would allow easier querying so handling the duplication may be worth the effort.
 
Storing the timezone with the time entry allows users to move timezones and still have the original data. So yes, each user should have a local timezone when displaying data, and he could then see that the recorded times were made in another one. That would cover your supervisor case. A bit esoteric (unusual) but it might be useful to some people.
 
Dave
 



Date: Tue, 20 Sep 2011 19:16:30 +1000
From: pal...@gm...
To: tsh...@li...
Subject: Re: [Tsheetx-developers] Timezones, time adjustment and UTC


David,
some comments on your notes:

What problems are eased by just storing the start date and duration? 
I think it is good to store both start, end and duration, but I don't know what problems you were hoping to fix with just start and duration. The reason I think it is good to store all three is it makes easy sql queries picking up the times records between dates a and b. 

There is quite a lot of code which does consolidation of times as well. This consolidation appears in simple and in month (although I'm still trying to understand what is going on in month). I would like to remove this consolidation and show each and every record. I also want to, where possible, to push the record selection logic back into sql. Let it deal with the complexities and just give us the records we need. Anyway, I digress.

As you mention in order to be precise, tsng needs to be able to display time records relative to the user's timezone, and I think that can be accomplished by associating a timezone with a user rather than with each time record. That is, the UTC times are retrieved from the db, and adjusted to the user's timezone for display. That's fine for one user. But how to handle one client who covers multiple timezones? Should the time be displayed in the timezone of the user displaying the data e.g. a supervisor's timezone? Not sure of the approach here.

Breaking entries crossing midnight. I think "midnight" should be relative to the user's timezone. However, I wonder how that will appear for the supervisor viewing a number of users times? Hmmm.. need to do a mockup.

Finally, I could remove the comments, but I would also like to remove the correction code. However, do other users have problems with times being re-adjusted, or am I the only one? I remember Scott didn't have problems at all, but he was on v4 of php. (There was one sourceforge comment recently from someone who had times being adjusted.)

Peter