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

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

Hey Mark,

Hmm, an interesting option, storing the timezone in effect when the data is
entered.  I think this could work, but I believe the logic to "fix things
up" for the viewer could get quite hairy.  It would be interesting to see
how each option would look and what issues each creates in php (option1:
store everything in utc; option2 store the active timezone).

For any option to work well, we need to pay very close attention to how
things work around daylight saving time changes.  If it can handle those
well, and non-ambiguously, it would certainly be worthy of strong
consideration.

-Scott

On Tue, Sep 20, 2011 at 12:07 PM, Mark Wrightson
<ma...@rw...>wrote:

>  Hi,
>
> David and Peter make some good comments about handling different
> timezones.  Whilst it isn't an issue in the UK, the problem is exacerbated
> in countries that work across different timezones or a business is
> multi-national.  I think the times should store the timezone that was
> entered.  This would then enable an option to either display dates in their
> local timezone or in their default timezone.  It also means that if a user
> changes location, old data is not reliant upon a user configuration setting
> which will ensure data integrity is maintained.
>
> Two ways of doing this is either have a timezone field or to use w3c date
> time format (i vote for the later option).  In terms of the date conversions
> I think OO has an easy way of achieving this.  When task times are extracted
> from the database, if they are created into objects, functions within that
> class can be written to retrieve the getLocalStartTime(),
> getActualStartTime(), getDuration() etc
>
> Scott also makes the good point of avoiding the automatic conversions of
> times by mysql.  We have much more control in PHP and allows the system to
> be more easily ported to other database systems if required at a later date.
>
> I also think we should have either a duration or end date in the database.
> The calculation of duration is a low cost computation so could be calculated
> with ease each time.  I propose two fields, start_time and end_time.
> Database manipulations (if required) are much easier when dealing with date
> values when compared to a duration field that would contain the no. of
> seconds or minutes since the start time.
>
> As the midnight point is variable across timezones, I feel it is not
> possible to split times in the database into individual days.  I quite like
> that a task can cross between days as I often work late in the evening so
> would prefer to have only one task.  The other problem with splitting tasks
> is that you then have a comment against each time that has to be duplicated.
>
> My vote is to keep the system as versatile as possible and I think this is
> possible.  All the code is already there to handle times that cross day
> boundaries.  There is no reason not to keep it in there.
>
> Peter - if you would like to look at creating a Times Object class we can
> enter a discussion and implement an example on one of the calendars.
>
> All the best
>
>
> Mark
>
> _____________________________________________
>
> Mob:  07725 695178
> Email: ma...@rw...
>
>
> On 20/09/2011 15:54, Scott Miller wrote:
>
> Hey guys,
>
>  Good to see this discussion and I'm looking forward to hearing about how
> it all gets solved.
>
>  End times were needed because it was too large a jump to go from what we
> had, having entries that could span across midnight, to having the system
> break those into multiple entries during database insertion.  I think, once
> the system stores dates in UTC format, and entries are restricted to not
> being able to cross UTC date boundries, then the ending date/time field can
> be removed; the duration field is all that would be needed.
>
>  I would strongly encourage you to use a non-date field type in the
> database to store large integers for the required timestamps (I'd suggest
> using 64 bits), and allow only PHP to do the date/time conversions needed.
>  Although it is possible to get mysql to do some of this work for you, I
> believe going down that path greatly reduces the flexibility of the system,
> and introduces the non-trivial task of ensuring that mysql is loaded with
> the correct timezone data; and some installations may not have the rights to
> be able to add the mysql timezone data.  And if mysql is allowed to muck
> with the timestamps before returning them to the application, debugging
> problems with date/time becomes much more involved now that you have another
> system that could introduce incorrect "adjustments".
>
>  -Scott L. Miller
>
> On Tue, Sep 20, 2011 at 7:10 AM, David Thompson <
> tom...@us...> wrote:
>
>>  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
>>
>>
>> ------------------------------------------------------------------------------
>> 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
>>
>>
>
>
> ------------------------------------------------------------------------------
> 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 lis...@li...https://lists.sourceforge.net/lists/listinfo/tsheetx-developers
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>