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

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

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
>
>