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

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

On the date front, I have played around on paper with a short example. 
Here it is:

User 1 TZ AU/Melbourne +10hrs ahead of UTC
Record 1 real 0800-1700 stored as -0200 0700  TZ AU/Melbourne
Record 2 real 1500-2400 stored as 0500 1400  TZ AU/Melbourne

User 2 TZ NZ/Auckland +12hrs ahead of UTC
Record 3 real 0800-1700 stored as -0400 0500 TZ NZ/Auckland
Record 4 real 1500-2400 stored as 0300 1200 TZ NZ/Auckland

user 3 TZ UK/London +1 hours
Record 5 real 0800-1700 stored as 0700 1600 TZ UK/London
Record 6 real 1500-2400 stored as 1400 2300 TZ UK/London

So lets say I want to display these records, and I am in the Melbourne 
timezone.
I want to search the database for records from midnight to midnight my 
time. I change the "midnight to midnight" to UTC using my timezone and 
search the database for records between -1000 and 1400
Now these are to be displayed in my timezone, so I add my TZ value to 
all of them. That makes them relative to my timezone.

R1  0800-1700 User 1
R2  1500-2400 user 1
R3  0600-1500 User 2
R4  1300-2200 User 2
R5  1700-0200-next day User 3
R6  2400-1300-next day User 3

As a manager I might want to see what hours my international employees 
worked. i can retrieve the records using some approach I haven't figured 
out yet to get the time records of people who say worked yesterday. 
Assuming I got the same six records, they would then be displayed each 
in its own timezone. I would add the specific timezone of each record, 
and that displays the times relative to the individual user's timezones:

R1  0800-1700 User 1 TZ AU/Melbourne
R2  1500-2400 user 1 TZ AU/Melbourne
R3  0800-1700 User 2 TZ NZ/Auckland
R4  1500-2400 User 2 TZ NZ/Auckland
R5  0800-1700 User 3 TZ UK/London
R6  1500-2400 User 3 TZ UK/London

The above format would probably be the data that would form part of an 
invoice for the three consultants.

So far, it it clear to me that the times are stored in the database 
times table in UTC, and a timezone descriptor is also stored in the 
times table. Mysql has no support at all for timezones, but php does. 
Hence calculations should be done in php. Also, I think having data in 
UTC makes sql record selection easier when referring to a range of dates.

php routines are timezone and daylight saving time aware. They can 
accurately provide a date in the future, based on dst. However, since 
dst beginning and ending dates change often, the code might not always 
work correctly, particularly when creating future dates. See here for a 
discussion and recommendation: 
http://phpvikinger.net/storing-date-time-in-database.html

What is not clear yet is how the php routines would handle this 
situation, although the above reference suggests it would.
user 1 clocks some time e.g. Monday 1/9 0800-1700 in standard time. The 
following weekend DST is introduced and he clocks in after on Monday 8/9 
0800-1700. When he displays the records for the past couple of weeks, 
will he see:

Monday 1/9 0800-1700
Monday 8/9 0800-1700
or
Monday 1/9 0800-1700
Monday 8/9 0900-1800

That's all for now folks. Let's hear your thoughts

Peter


On 09/21/2011 08:15 AM, Mark Wrightson wrote:
> Hi Scott,
>
> You are right that the management reports don't currently do anything 
> with clocking times other than durations but looking forward I can see 
> a requirement for instance that allows reports to be generated that 
> show users that are clocking hours outside the "usual hours of 
> business".  If a manager wants to create a profile that highlights 
> erroneous clockings, timezone becomes an important factor.
>
> Information can always be diluted at a later stage, but if it is lost 
> at the initial stage it can never be recovered.  tsng could be written 
> such that timezone is never accounted for, but if the db contains the 
> information, the system is at least capable of adding that 
> functionality at a later date.
>
> I'm an advocate of the "design for the future principle" - as in 
> consider the future potential the system could have and put some of 
> the building blocks in place that will facilitate that expansion.  The 
> expansion may never happen but at least the capability was available 
> without a significant redesign.
>
> As we are discussing clocking times, I also think the user rates 
> should be part of the individual clock times, as a individual users 
> rate may change over a period of time, but the old clockings should 
> not neccessarily be updated with the new rate. .../but thats another 
> discussion!/
>
> Cheers
> Mark
> _____________________________________________
>
> Mob:  07725 695178
> Email:ma...@rw...
>
> On 20/09/2011 19:40, Scott Miller wrote:
>> But it being "lossy" is not necessarily a bad thing.  The database 
>> itself doesn't need to know the timezone.  It's only the user looking 
>> at the data that may care about when during the user's day a task 
>> happened.  Taking UTC time and converting it to the current user's 
>> timezone may be better than your proposal; it's way to early in the 
>> discussions for me to predict which will win out.
>>
>> If we consider the real "ends" that a timesheet system provides, 
>> the management reports that are generated later, they really don't 
>> care when the start/stop times were, they only care about the amount 
>> of time that was spent within a given time frame.  So all the 
>> monkeying about with timezones etc. is only needed to keep the end 
>> user happy that their data is entered correctly.  Unfortunately this 
>> makes up 90% or more of the use of the system.  So, which ever way is 
>> most efficient, in terms of maintainability plus user experience, at 
>> creating a "good" experience for the end user is the one that should 
>> "win".
>>
>> -Scott
>