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

[Peter L. <pal...@gm...>]

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

On 09/20/2011 06:35 PM, David Thompson wrote:
> 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
>