Menu
▾
▴
Re: [Tsheetx-developers] Timezones, time adjustment and UTC
|
From: Mark W. <ma...@rw...> - 2011-09-20 17:48:58
|
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Scott,<br>
<br>
Some work does need to be done to confirm this, but I think all of
the logic could be quite neatly encapsulated in a single TaskTime
object class. The conversion between w3c dates, utc dates and
locale dates is quite easy and could be made into functions of a
TaskTime object relatively easy.<br>
<br>
Work to confirm this would be needed through. At least if the
timezone is stored, the database contains all of the required data
to show whatever you need. whereas storing as utc is 'lossy' in
that we lose the timezone information.<br>
<br>
Cheers<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 18:25, Scott Miller wrote:
<blockquote
cite="mid:CAL...@ma..."
type="cite">Hey Mark,
<div><br>
</div>
<div>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).</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>-Scott<br>
<br>
<div class="gmail_quote">On Tue, Sep 20, 2011 at 12:07 PM, Mark
Wrightson <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:ma...@rw...">ma...@rw...</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div 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
<div class="im"><br>
<br>
Mark<br>
<pre cols="72">_____________________________________________
Mob: 07725 695178
Email: <a moz-do-not-send="true" href="mailto:ma...@rw..." target="_blank">ma...@rw...</a></pre>
<br>
</div>
<div>
<div class="h5"> On 20/09/2011 15:54, Scott Miller
wrote:
<blockquote 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..."
target="_blank">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><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><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..."
target="_blank">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></fieldset>
<br>
<pre>------------------------------------------------------------------------------
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 moz-do-not-send="true" href="http://p.sf.net/sfu/splunk-d2dcopy1" target="_blank">http://p.sf.net/sfu/splunk-d2dcopy1</a></pre>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
Tsheetx-developers mailing list
<a moz-do-not-send="true" href="mailto:Tsh...@li..." target="_blank">Tsh...@li...</a>
<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>
</pre>
</blockquote>
</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>
</blockquote>
</body>
</html>
|
