Menu
▾
▴
Re: [Tsheetx-developers] Timezones, time adjustment and UTC
|
From: Mark W. <ma...@rw...> - 2011-09-20 17:07:39
|
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi, <br>
<br>
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.<br>
<br>
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<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
All the best<br>
<br>
Mark<br>
<pre class="moz-signature" cols="72">_____________________________________________
Mob: 07725 695178
Email: <a class="moz-txt-link-abbreviated" href="mailto:ma...@rw...">ma...@rw...</a></pre>
<br>
On 20/09/2011 15:54, Scott Miller wrote:
<blockquote
cite="mid:CALMOH3cHgeoR1t6K+FKeknaBxoAvuuEXd18Yj33pT=K-v...@ma..."
type="cite">Hey guys,
<div><br>
</div>
<div>Good to see this discussion and I'm looking forward to
hearing about how it all gets solved.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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".</div>
<div><br>
</div>
<div>-Scott L. Miller<br>
<br>
<div class="gmail_quote">On Tue, Sep 20, 2011 at 7:10 AM, David
Thompson <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:tom...@us...">tom...@us...</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div>
<div dir="ltr">
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.<br>
<br>
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.<br>
<br>
Dave<br>
<br>
<div>
<hr>
Date: Tue, 20 Sep 2011 19:16:30 +1000
<div class="im"><br>
From: <a moz-do-not-send="true"
href="mailto:pal...@gm..." target="_blank">pal...@gm...</a><br>
To: <a moz-do-not-send="true"
href="mailto:tsh...@li..."
target="_blank">tsh...@li...</a><br>
</div>
Subject: Re: [Tsheetx-developers] Timezones, time
adjustment and UTC
<div class="im"><br>
<br>
David,<br>
some comments on your notes:<br>
<br>
What problems are eased by just storing the start
date and duration? <br>
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. <br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.)<br>
<br>
Peter<br>
</div>
</div>
</div>
</div>
<br>
------------------------------------------------------------------------------<br>
All the data continuously generated in your IT
infrastructure contains a<br>
definitive record of customers, application performance,
security<br>
threats, fraudulent activity and more. Splunk takes this
data and makes<br>
sense of it. Business sense. IT sense. Common sense.<br>
<a moz-do-not-send="true"
href="http://p.sf.net/sfu/splunk-d2dcopy1" target="_blank">http://p.sf.net/sfu/splunk-d2dcopy1</a><br>
_______________________________________________<br>
Tsheetx-developers mailing list<br>
<a moz-do-not-send="true"
href="mailto:Tsh...@li...">Tsh...@li...</a><br>
<a moz-do-not-send="true"
href="https://lists.sourceforge.net/lists/listinfo/tsheetx-developers"
target="_blank">https://lists.sourceforge.net/lists/listinfo/tsheetx-developers</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">------------------------------------------------------------------------------
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.
<a class="moz-txt-link-freetext" href="http://p.sf.net/sfu/splunk-d2dcopy1">http://p.sf.net/sfu/splunk-d2dcopy1</a></pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Tsheetx-developers mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Tsh...@li...">Tsh...@li...</a>
<a class="moz-txt-link-freetext" href="https://lists.sourceforge.net/lists/listinfo/tsheetx-developers">https://lists.sourceforge.net/lists/listinfo/tsheetx-developers</a>
</pre>
</blockquote>
</body>
</html>
|
