Menu
▾
▴
Re: [Tsheetx-developers] Timezones, time adjustment and UTC
|
From: Mark W. <ma...@rw...> - 2011-09-20 22:15:49
|
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Scott, <br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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. ...<i>but thats
another discussion!</i><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 19:40, Scott Miller wrote:
<blockquote
cite="mid:CAL...@ma..."
type="cite">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.
<div>
<br>
<div>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".</div>
<div><br>
</div>
<div>-Scott<br>
<div><br>
<div class="gmail_quote">On Tue, Sep 20, 2011 at 12:48 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 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
<div class="im"><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 18:25, Scott Miller
wrote:
<blockquote 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..."
target="_blank">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><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> 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..."
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>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</body>
</html>
|
