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

[Scott M. <sco...@gm...>]

Excellent discussions.  Two things:
     Mysql does have support for timezones, but as I stated before, I think
it would be a mistake to use it:
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

     Regardless of when/how DST times dates change, as long as we're storing
a unix-type timestamp, we should never have to worry about whether our code
works.  The DST calculations are handled by system level functions that can
be updated as things change.  On the other hand, whether historical DST
information is kept, and whether looking up times stored 5 to 10 years ago
would ever work perfectly correctly, I'm figuring not, but that far out,
it's probably not needed either. (I was purging the previous years data
around July at my previous employer, well after all the reports were
generated and tax season was over.)

-Scott

On Wed, Sep 21, 2011 at 2:08 AM, Peter Lazarus <pal...@gm...> wrote:

>  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
>
>
>
>
> ------------------------------------------------------------------------------
> All the data continuously generated in your IT infrastructure contains a
> definitive record of customers, application performance, security
> threats, fraudulent activity and more. Splunk takes this data and makes
> sense of it. Business sense. IT sense. Common sense.
> http://p.sf.net/sfu/splunk-d2dcopy1
> _______________________________________________
> Tsheetx-developers mailing list
> Tsh...@li...
> https://lists.sourceforge.net/lists/listinfo/tsheetx-developers
>
>