Menu
▾
▴
tsheetx-developers
|
From: Peter L. <pal...@gm...> - 2011-09-20 05:45:19
|
I have been wrestling with why some of the timestamps in my database are
14 or 15 hours out of whack. Specifically the end_time is wrong. If I
clockin/out at 10:00-11:00 am, the timestamps in the db are start_time
of 10am and end_time of 2am the next day. Duration shows as 60 mins. So
I think what is happening is that function fixStartEndDuration and
fix_entry_endstamp are changing the end times.
There is this comment at the start of function fixStartEndDuration
//Due to a bug in mysql with converting to unix timestamp from the
string,
//we are going to use php's strtotime to make the timestamp
from the string.
//the problem has something to do with timezones.
Can anyone shed some light on what this bug might have been? Was it a
mysql 4.x bug? The url
http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php mentioned in the
same function talks about handling timezones in php. It appears to me
that this web site may have changed since the url was pasted into the
function as I remember there were a few added comments then which are no
longer there. So any description of what bug there was, is no longer there.
This problem is most likely influenced by the system timezone, which
mysql uses, and whatever timezone is given to php. But I haven't
investigated that on my system yet.
In a future release i would like to change the way dates/times are
stored in the database. I think the database needs to keep time in UTC
only. Now we could change the mysql datetime fields to timestamp. Values
for |TIMESTAMP| <http://dev.mysql.com/doc/refman/5.5/en/datetime.html>
columns are converted from the current time zone to UTC for storage, and
from UTC to the current time zone for retrieval. datetime fields store
time in local time with no conversion i.e. set by the server's timezone.
See here for a discussion
http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ with
some suggestions in this blog that datetime fields are better for
performance, possibly because the database does not have to do date
conversions.
There is also a short howto /user experience on converting your database
to using UTC:
http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration
Anyway, the reason I want to convert to UTC in the database is to remove
the probable timezone problems that fixStartEndDuration seems to be
trying to correct. The more important reason however is to support
multiple timezones. Since we have made a major effort to
internationalise the language in the web pages, we should also manage
the timestamps better. Being able to manage multiple timezones would
mean allowing each user to save and display his times in his own
timezone. The application then becomes a useful application for
international use, where users are in many different timezones.
Peter
|
|
From: Mark W. <ma...@rw...> - 2011-09-20 06:58:06
|
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Peter, <br>
<br>
I scan read your supporting url's and I just wanted to clarify what
tsng currently does wrt timezones? You mentioned mysql timestamp
fields and datetime fields. The supporting information talks about
changing to datetime fields whereas you discuss the opposite?<br>
<br>
Is there any added value in storing dates using the w3c date format?
i.e. (<code><span class="html">2007-05-06T02:03:04-06:00)</span></code><br>
<br>
<i>Extract from <a class="moz-txt-link-freetext" href="http://php.net/manual/en/function.date.php">http://php.net/manual/en/function.date.php</a><br>
<br>
</i>
<blockquote><code><span class="html">Just in case anyone else is
looking for an easy-to-find equivalent for W3C Datetime or
date("c") in a previous version of php, here's one I did.
Hope it helps someone.
</span></code><br>
<code><span class="html">
</span></code><br>
<code><span class="html">
<span class="default"><?php
</span></span></code><br>
<code><span class="html"><span class="default">
</span><span class="keyword">function </span><span
class="default">w3cDate</span><span class="keyword">(</span><span
class="default">$time</span><span class="keyword">=</span><span
class="default">NULL</span><span class="keyword">)
</span></span></code><br>
<code><span class="html"><span class="keyword">
{
</span></span></code><br>
<code><span class="html"><span class="keyword">
if (empty(</span><span class="default">$time</span><span
class="keyword">))
</span></span></code><br>
<code><span class="html"><span class="keyword">
</span><span class="default">$time </span><span
class="keyword">= </span><span class="default">time</span><span
class="keyword">();
</span></span></code><br>
<code><span class="html"><span class="keyword">
</span><span class="default">$offset </span><span
class="keyword">= </span><span class="default">date</span><span
class="keyword">(</span><span class="string">"O"</span><span
class="keyword">,</span><span class="default">$time</span><span
class="keyword">);
</span></span></code><br>
<code><span class="html"><span class="keyword">
return </span><span class="default">date</span><span
class="keyword">(</span><span class="string">"Y-m-d\TH:i:s"</span><span
class="keyword">,</span><span class="default">$time</span><span
class="keyword">).</span><span class="default">substr</span><span
class="keyword">(</span><span class="default">$offset</span><span
class="keyword">,</span><span class="default">0</span><span
class="keyword">,</span><span class="default">3</span><span
class="keyword">).</span><span class="string">":"</span><span
class="keyword">.</span><span class="default">substr</span><span
class="keyword">(</span><span class="default">$offset</span><span
class="keyword">,-</span><span class="default">2</span><span
class="keyword">);
</span></span></code><br>
<code><span class="html"><span class="keyword">
}
</span></span></code><br>
<code><span class="html"><span class="keyword">
</span><span class="default">?>
</span></span></code><br>
<code><span class="html"><span class="default">
</span>
</span></code><br>
<code><span class="html">
Examples: </span></code><br>
<code><span class="html">
echo w3cDate(); //2008-11-18T12:15:18-07:00
</span></code><br>
<code><span class="html">
echo w3cDate(mktime(2,3,4,5,6,2007));
//2007-05-06T02:03:04-06:00</span></code><br>
</blockquote>
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 06:45, Peter Lazarus wrote:
<blockquote cite="mid:4E7...@gm..." type="cite">
<meta http-equiv="content-type" content="text/html;
charset=ISO-8859-1">
I have been wrestling with why some of the timestamps in my
database are 14 or 15 hours out of whack. Specifically the
end_time is wrong. If I clockin/out at 10:00-11:00 am, the
timestamps in the db are start_time of 10am and end_time of 2am
the next day. Duration shows as 60 mins. So I think what is
happening is that function fixStartEndDuration and
fix_entry_endstamp are changing the end times. <br>
<br>
There is this comment at the start of function fixStartEndDuration<br>
//Due to a bug in mysql with converting to unix timestamp from
the string,<br>
//we are going to use php's strtotime to make the
timestamp from the string.<br>
//the problem has something to do with timezones.<br>
<br>
Can anyone shed some light on what this bug might have been? Was
it a mysql 4.x bug? The url <a moz-do-not-send="true"
class="moz-txt-link-freetext"
href="http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php">http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php</a>
mentioned in the same function talks about handling timezones in
php. It appears to me that this web site may have changed since
the url was pasted into the function as I remember there were a
few added comments then which are no longer there. So any
description of what bug there was, is no longer there.<br>
<br>
This problem is most likely influenced by the system timezone,
which mysql uses, and whatever timezone is given to php. But I
haven't investigated that on my system yet.<br>
<br>
In a future release i would like to change the way dates/times are
stored in the database. I think the database needs to keep time in
UTC only. Now we could change the mysql datetime fields to
timestamp. Values for <a moz-do-not-send="true"
href="http://dev.mysql.com/doc/refman/5.5/en/datetime.html"
title="10.3.1. The DATETIME, DATE, and TIMESTAMP Types"><code
class="literal">TIMESTAMP</code></a> columns are converted
from the current time zone to UTC for storage, and from UTC to the
current time zone for retrieval. datetime fields store time in
local time with no conversion i.e. set by the server's timezone.
See here for a discussion <a moz-do-not-send="true"
class="moz-txt-link-freetext"
href="http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/">http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/</a>
with some suggestions in this blog that datetime fields are better
for performance, possibly because the database does not have to do
date conversions.<br>
<br>
There is also a short howto /user experience on converting your
database to using UTC: <a moz-do-not-send="true"
class="moz-txt-link-freetext"
href="http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration">http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration</a><br>
<br>
Anyway, the reason I want to convert to UTC in the database is to
remove the probable timezone problems that fixStartEndDuration
seems to be trying to correct. The more important reason however
is to support multiple timezones. Since we have made a major
effort to internationalise the language in the web pages, we
should also manage the timestamps better. Being able to manage
multiple timezones would mean allowing each user to save and
display his times in his own timezone. The application then
becomes a useful application for international use, where users
are in many different timezones.<br>
<br>
Peter<br>
<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>
|
|
From: Peter L. <pal...@gm...> - 2011-09-20 07:22:07
|
Mark, just to clarify my intent. It is to retain datetime fields. My idea is to adjust the times stored so that they go from the local time into the database as utc, stored in datetime fields. When retrieved from the db they will be converted to the user's local time zone. Don't think there is value in using w3c date, but will review it. Peter On 09/20/2011 04:57 PM, Mark Wrightson wrote: > Hi Peter, > > I scan read your supporting url's and I just wanted to clarify what > tsng currently does wrt timezones? You mentioned mysql timestamp > fields and datetime fields. The supporting information talks about > changing to datetime fields whereas you discuss the opposite? > > Is there any added value in storing dates using the w3c date format? > i.e. (|2007-05-06T02:03:04-06:00)| > > /Extract from http://php.net/manual/en/function.date.php > > / > > |Just in case anyone else is looking for an easy-to-find > equivalent for W3C Datetime or date("c") in a previous version of > php, here's one I did. Hope it helps someone. | > || > |<?php | > |function w3cDate($time=NULL) | > |{ | > | if (empty($time)) | > |$time = time(); | > |$offset = date("O",$time); | > | return > date("Y-m-d\TH:i:s",$time).substr($offset,0,3).":".substr($offset,-2); > | > |} | > |?> | > || > |Examples: | > |echo w3cDate(); //2008-11-18T12:15:18-07:00 | > |echo w3cDate(mktime(2,3,4,5,6,2007)); //2007-05-06T02:03:04-06:00| > > Cheers > Mark > _____________________________________________ > > Mob: 07725 695178 > Email:ma...@rw... > > On 20/09/2011 06:45, Peter Lazarus wrote: >> I have been wrestling with why some of the timestamps in my database >> are 14 or 15 hours out of whack. Specifically the end_time is wrong. >> If I clockin/out at 10:00-11:00 am, the timestamps in the db are >> start_time of 10am and end_time of 2am the next day. Duration shows >> as 60 mins. So I think what is happening is that function >> fixStartEndDuration and fix_entry_endstamp are changing the end times. >> >> There is this comment at the start of function fixStartEndDuration >> //Due to a bug in mysql with converting to unix timestamp from >> the string, >> //we are going to use php's strtotime to make the timestamp >> from the string. >> //the problem has something to do with timezones. >> >> Can anyone shed some light on what this bug might have been? Was it a >> mysql 4.x bug? The url >> http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php mentioned in >> the same function talks about handling timezones in php. It appears >> to me that this web site may have changed since the url was pasted >> into the function as I remember there were a few added comments then >> which are no longer there. So any description of what bug there was, >> is no longer there. >> >> This problem is most likely influenced by the system timezone, which >> mysql uses, and whatever timezone is given to php. But I haven't >> investigated that on my system yet. >> >> In a future release i would like to change the way dates/times are >> stored in the database. I think the database needs to keep time in >> UTC only. Now we could change the mysql datetime fields to timestamp. >> Values for |TIMESTAMP| >> <http://dev.mysql.com/doc/refman/5.5/en/datetime.html> columns are >> converted from the current time zone to UTC for storage, and from UTC >> to the current time zone for retrieval. datetime fields store time in >> local time with no conversion i.e. set by the server's timezone. See >> here for a discussion >> http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ >> with some suggestions in this blog that datetime fields are better >> for performance, possibly because the database does not have to do >> date conversions. >> >> There is also a short howto /user experience on converting your >> database to using UTC: >> http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration >> >> Anyway, the reason I want to convert to UTC in the database is to >> remove the probable timezone problems that fixStartEndDuration seems >> to be trying to correct. The more important reason however is to >> support multiple timezones. Since we have made a major effort to >> internationalise the language in the web pages, we should also manage >> the timestamps better. Being able to manage multiple timezones would >> mean allowing each user to save and display his times in his own >> timezone. The application then becomes a useful application for >> international use, where users are in many different timezones. >> >> 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 list > Tsh...@li... > https://lists.sourceforge.net/lists/listinfo/tsheetx-developers |
|
From: David T. <tom...@us...> - 2011-09-20 08:35:09
|
Peter, good that you're looking at this kind of thing, because it is at the core of the application (and quite a few problems).
Historically, I wanted to move away from the original design for time entries that was storing the start and end dates. It eases a lot of problems to store just the start date and a duration. But this is only half implemented, meaning we have three fields: start, end and duration. This was meant to be temporary, but we haven't got around to fixing it. My goal would still be to remove the end-dates.
As to how we store start (and end) dates, I think you are right - it should be as neutral as possible, i.e. UTC. But then in order be precise, each time entry should store the user's current timezone too, so that we can precisely retrieve the original value. I did not realise that mySQL has a conversion too, we have to check the options (system, php and mysql timezones) to store the right type/value.
One issue was to break up entries that crossed "midnight", this was to make querying time periods easier. But since "midnight" is relative to your timezone, I'm not sure that it will help in the long term (we have to analyse start-time and duration anyway).
The comments about the bug in mySQL are older than my association with the project, I think we can happily correct this now (and remove the comments)...
Cheers
Date: Tue, 20 Sep 2011 15:45:07 +1000
From: pal...@gm...
To: tsh...@li...
Subject: [Tsheetx-developers] Timezones, time adjustment and UTC
I have been wrestling with why some of the timestamps in my database are 14 or 15 hours out of whack. Specifically the end_time is wrong. If I clockin/out at 10:00-11:00 am, the timestamps in the db are start_time of 10am and end_time of 2am the next day. Duration shows as 60 mins. So I think what is happening is that function fixStartEndDuration and fix_entry_endstamp are changing the end times.
There is this comment at the start of function fixStartEndDuration
//Due to a bug in mysql with converting to unix timestamp from the string,
//we are going to use php's strtotime to make the timestamp from the string.
//the problem has something to do with timezones.
Can anyone shed some light on what this bug might have been? Was it a mysql 4.x bug? The url http://jokke.dk/blog/2007/07/timezones_in_mysql_and_php mentioned in the same function talks about handling timezones in php. It appears to me that this web site may have changed since the url was pasted into the function as I remember there were a few added comments then which are no longer there. So any description of what bug there was, is no longer there.
This problem is most likely influenced by the system timezone, which mysql uses, and whatever timezone is given to php. But I haven't investigated that on my system yet.
In a future release i would like to change the way dates/times are stored in the database. I think the database needs to keep time in UTC only. Now we could change the mysql datetime fields to timestamp. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval. datetime fields store time in local time with no conversion i.e. set by the server's timezone. See here for a discussion http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ with some suggestions in this blog that datetime fields are better for performance, possibly because the database does not have to do date conversions.
There is also a short howto /user experience on converting your database to using UTC: http://stackoverflow.com/questions/2982897/php-mysql-time-zone-migration
Anyway, the reason I want to convert to UTC in the database is to remove the probable timezone problems that fixStartEndDuration seems to be trying to correct. The more important reason however is to support multiple timezones. Since we have made a major effort to internationalise the language in the web pages, we should also manage the timestamps better. Being able to manage multiple timezones would mean allowing each user to save and display his times in his own timezone. The application then becomes a useful application for international use, where users are in many different timezones.
Peter
|
|
From: Peter L. <pal...@gm...> - 2011-09-20 09:16:43
|
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 On 09/20/2011 06:35 PM, David Thompson wrote: > Peter, good that you're looking at this kind of thing, because it is > at the core of the application (and quite a few problems). > > Historically, I wanted to move away from the original design for time > entries that was storing the start and end dates. It eases a lot of > problems to store just the start date and a duration. But this is only > half implemented, meaning we have three fields: start, end and > duration. This was meant to be temporary, but we haven't got around to > fixing it. My goal would still be to remove the end-dates. > > As to how we store start (and end) dates, I think you are right - it > should be as neutral as possible, i.e. UTC. But then in order be > precise, each time entry should store the user's current timezone too, > so that we can precisely retrieve the original value. I did not > realise that mySQL has a conversion too, we have to check the options > (system, php and mysql timezones) to store the right type/value. > > One issue was to break up entries that crossed "midnight", this was to > make querying time periods easier. But since "midnight" is relative to > your timezone, I'm not sure that it will help in the long term (we > have to analyse start-time and duration anyway). > > The comments about the bug in mySQL are older than my association with > the project, I think we can happily correct this now (and remove the > comments)... > > Cheers > |
|
From: David T. <tom...@us...> - 2011-09-20 12:10:29
|
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 |
|
From: Scott M. <sco...@gm...> - 2011-09-20 14:54:21
|
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 > > |
|
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>
|
|
From: Scott M. <sco...@gm...> - 2011-09-20 17:25:25
|
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 > > |
|
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>
|
|
From: Scott M. <sco...@gm...> - 2011-09-20 18:40:49
|
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. 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". -Scott On Tue, Sep 20, 2011 at 12:48 PM, Mark Wrightson <ma...@rw...>wrote: > Hi Scott, > > 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. > > 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. > > Cheers > > Mark > > _____________________________________________ > > Mob: 07725 695178 > Email: ma...@rw... > > > On 20/09/2011 18:25, Scott Miller wrote: > > 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 >> >> > |
|
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>
|
|
From: Peter L. <pal...@gm...> - 2011-09-21 07:08:22
|
Scott, Mark, so we are gathering some requirements here, which is good. Not only should tsng handle multiple timezones, but also daylight saving. And user rates. There could be multiple user rates per person as well. One of the reasons I introduced a user type "contractor, employee" was to be able to see how much profit a consulting job was making. For example the contractor costs me $x and I charge him out at $y, and I can then see the difference. Mark is also right, there is a need for one person to have different charge rates for different tasks or projects. And some charging is done on an hourly basis and some on a fixed 8 hour day. Peter On 09/21/2011 08:15 AM, Mark Wrightson wrote: > Hi Scott, > > 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. > > 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. > > 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. > > 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. .../but thats another > discussion!/ > > Cheers > Mark > _____________________________________________ > > Mob: 07725 695178 > Email:ma...@rw... > > On 20/09/2011 19:40, Scott Miller wrote: >> 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. >> >> 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". >> >> -Scott >> >> On Tue, Sep 20, 2011 at 12:48 PM, Mark Wrightson >> <ma...@rw... <mailto:ma...@rw...>> wrote: >> >> Hi Scott, >> >> 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. >> >> 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. >> >> Cheers >> >> Mark >> > |
|
From: Peter L. <pal...@gm...> - 2011-09-21 07:09:06
|
On the date front, I have played around on paper with a short example. Here it is: User 1 TZ AU/Melbourne +10hrs ahead of UTC Record 1 real 0800-1700 stored as -0200 0700 TZ AU/Melbourne Record 2 real 1500-2400 stored as 0500 1400 TZ AU/Melbourne User 2 TZ NZ/Auckland +12hrs ahead of UTC Record 3 real 0800-1700 stored as -0400 0500 TZ NZ/Auckland Record 4 real 1500-2400 stored as 0300 1200 TZ NZ/Auckland user 3 TZ UK/London +1 hours Record 5 real 0800-1700 stored as 0700 1600 TZ UK/London Record 6 real 1500-2400 stored as 1400 2300 TZ UK/London So lets say I want to display these records, and I am in the Melbourne timezone. I want to search the database for records from midnight to midnight my time. I change the "midnight to midnight" to UTC using my timezone and search the database for records between -1000 and 1400 Now these are to be displayed in my timezone, so I add my TZ value to all of them. That makes them relative to my timezone. R1 0800-1700 User 1 R2 1500-2400 user 1 R3 0600-1500 User 2 R4 1300-2200 User 2 R5 1700-0200-next day User 3 R6 2400-1300-next day User 3 As a manager I might want to see what hours my international employees worked. i can retrieve the records using some approach I haven't figured out yet to get the time records of people who say worked yesterday. Assuming I got the same six records, they would then be displayed each in its own timezone. I would add the specific timezone of each record, and that displays the times relative to the individual user's timezones: R1 0800-1700 User 1 TZ AU/Melbourne R2 1500-2400 user 1 TZ AU/Melbourne R3 0800-1700 User 2 TZ NZ/Auckland R4 1500-2400 User 2 TZ NZ/Auckland R5 0800-1700 User 3 TZ UK/London R6 1500-2400 User 3 TZ UK/London The above format would probably be the data that would form part of an invoice for the three consultants. So far, it it clear to me that the times are stored in the database times table in UTC, and a timezone descriptor is also stored in the times table. Mysql has no support at all for timezones, but php does. Hence calculations should be done in php. Also, I think having data in UTC makes sql record selection easier when referring to a range of dates. php routines are timezone and daylight saving time aware. They can accurately provide a date in the future, based on dst. However, since dst beginning and ending dates change often, the code might not always work correctly, particularly when creating future dates. See here for a discussion and recommendation: http://phpvikinger.net/storing-date-time-in-database.html What is not clear yet is how the php routines would handle this situation, although the above reference suggests it would. user 1 clocks some time e.g. Monday 1/9 0800-1700 in standard time. The following weekend DST is introduced and he clocks in after on Monday 8/9 0800-1700. When he displays the records for the past couple of weeks, will he see: Monday 1/9 0800-1700 Monday 8/9 0800-1700 or Monday 1/9 0800-1700 Monday 8/9 0900-1800 That's all for now folks. Let's hear your thoughts Peter On 09/21/2011 08:15 AM, Mark Wrightson wrote: > Hi Scott, > > 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. > > 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. > > 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. > > 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. .../but thats another > discussion!/ > > Cheers > Mark > _____________________________________________ > > Mob: 07725 695178 > Email:ma...@rw... > > On 20/09/2011 19:40, Scott Miller wrote: >> 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. >> >> 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". >> >> -Scott > |
|
From: Scott M. <sco...@gm...> - 2011-09-21 14:34:18
|
Excellent discussions. Two things:
Mysql does have support for timezones, but as I stated before, I think
it would be a mistake to use it:
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html
Regardless of when/how DST times dates change, as long as we're storing
a unix-type timestamp, we should never have to worry about whether our code
works. The DST calculations are handled by system level functions that can
be updated as things change. On the other hand, whether historical DST
information is kept, and whether looking up times stored 5 to 10 years ago
would ever work perfectly correctly, I'm figuring not, but that far out,
it's probably not needed either. (I was purging the previous years data
around July at my previous employer, well after all the reports were
generated and tax season was over.)
-Scott
On Wed, Sep 21, 2011 at 2:08 AM, Peter Lazarus <pal...@gm...> wrote:
> On the date front, I have played around on paper with a short example.
> Here it is:
>
> User 1 TZ AU/Melbourne +10hrs ahead of UTC
> Record 1 real 0800-1700 stored as -0200 0700 TZ AU/Melbourne
> Record 2 real 1500-2400 stored as 0500 1400 TZ AU/Melbourne
>
> User 2 TZ NZ/Auckland +12hrs ahead of UTC
> Record 3 real 0800-1700 stored as -0400 0500 TZ NZ/Auckland
> Record 4 real 1500-2400 stored as 0300 1200 TZ NZ/Auckland
>
> user 3 TZ UK/London +1 hours
> Record 5 real 0800-1700 stored as 0700 1600 TZ UK/London
> Record 6 real 1500-2400 stored as 1400 2300 TZ UK/London
>
> So lets say I want to display these records, and I am in the Melbourne
> timezone.
> I want to search the database for records from midnight to midnight my
> time. I change the "midnight to midnight" to UTC using my timezone and
> search the database for records between -1000 and 1400
> Now these are to be displayed in my timezone, so I add my TZ value to all
> of them. That makes them relative to my timezone.
>
> R1 0800-1700 User 1
> R2 1500-2400 user 1
> R3 0600-1500 User 2
> R4 1300-2200 User 2
> R5 1700-0200-next day User 3
> R6 2400-1300-next day User 3
>
> As a manager I might want to see what hours my international employees
> worked. i can retrieve the records using some approach I haven't figured out
> yet to get the time records of people who say worked yesterday. Assuming I
> got the same six records, they would then be displayed each in its own
> timezone. I would add the specific timezone of each record, and that
> displays the times relative to the individual user's timezones:
>
> R1 0800-1700 User 1 TZ AU/Melbourne
> R2 1500-2400 user 1 TZ AU/Melbourne
> R3 0800-1700 User 2 TZ NZ/Auckland
> R4 1500-2400 User 2 TZ NZ/Auckland
> R5 0800-1700 User 3 TZ UK/London
> R6 1500-2400 User 3 TZ UK/London
>
> The above format would probably be the data that would form part of an
> invoice for the three consultants.
>
> So far, it it clear to me that the times are stored in the database times
> table in UTC, and a timezone descriptor is also stored in the times table.
> Mysql has no support at all for timezones, but php does. Hence calculations
> should be done in php. Also, I think having data in UTC makes sql record
> selection easier when referring to a range of dates.
>
> php routines are timezone and daylight saving time aware. They can
> accurately provide a date in the future, based on dst. However, since dst
> beginning and ending dates change often, the code might not always work
> correctly, particularly when creating future dates. See here for a
> discussion and recommendation:
> http://phpvikinger.net/storing-date-time-in-database.html
>
> What is not clear yet is how the php routines would handle this situation,
> although the above reference suggests it would.
> user 1 clocks some time e.g. Monday 1/9 0800-1700 in standard time. The
> following weekend DST is introduced and he clocks in after on Monday 8/9
> 0800-1700. When he displays the records for the past couple of weeks, will
> he see:
>
> Monday 1/9 0800-1700
> Monday 8/9 0800-1700
> or
> Monday 1/9 0800-1700
> Monday 8/9 0900-1800
>
> That's all for now folks. Let's hear your thoughts
>
>
> Peter
>
>
> On 09/21/2011 08:15 AM, Mark Wrightson wrote:
>
> Hi Scott,
>
> 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.
>
> 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.
>
> 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.
>
> 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. ...*but thats another discussion!*
>
> Cheers
> Mark
>
> _____________________________________________
>
> Mob: 07725 695178
> Email: ma...@rw...
>
>
> On 20/09/2011 19:40, Scott Miller wrote:
>
> 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.
>
> 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".
>
> -Scott
>
>
>
>
> ------------------------------------------------------------------------------
> 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
>
>
|
|
From: Peter L. <pal...@gm...> - 2012-01-04 02:23:38
|
Hello everyone, welcome to 2012. After not doing much on TSNG 1.5.3 for a couple of months, I hope to do some more useful development in January. One of the tasks I have reviewed and upgraded is to ensure the security permissions for various forms are consistent with their usage, and appropriate access level. For example, ensuring all forms to do with the definition and maintenance of clients, projects, tasks, rates use the appropriate aclClients, aclProjects etc. I would like to now change the default values in TSNG 1.5.3 for some of these permissions as follows, since the default is to set them all to "Basic". The access levels for clients, projects, tasks, rates, expense categories and timesheet approval should be defaulted to "Manager". This is because basic users should not be able, by default, to add and delete clients, projects, rates etc. Can I get a general consensus on that one? Peter |
|
From: David T. <tom...@us...> - 2012-01-05 20:42:03
|
Hi there, happy new year too. By all means change the defaults, but the testing of the different settings is more important. I seem to remember some cases when I saw some bugs, but I have never tested it rigorously enough. But when you say 1.5.3 do you mean the trunk version, or the 2.0-demo branch? What is the state of both? Cheers > Date: Wed, 4 Jan 2012 13:23:27 +1100 > From: pal...@gm... > To: tsh...@li... > Subject: [Tsheetx-developers] Changing default permissions > > Hello everyone, > welcome to 2012. After not doing much on TSNG 1.5.3 for a couple of > months, I hope to do some more useful development in January. > > One of the tasks I have reviewed and upgraded is to ensure the security > permissions for various forms are consistent with their usage, and > appropriate access level. For example, ensuring all forms to do with the > definition and maintenance of clients, projects, tasks, rates use the > appropriate aclClients, aclProjects etc. > > I would like to now change the default values in TSNG 1.5.3 for some of > these permissions as follows, since the default is to set them all to > "Basic". The access levels for clients, projects, tasks, rates, expense > categories and timesheet approval should be defaulted to "Manager". This > is because basic users should not be able, by default, to add and delete > clients, projects, rates etc. > > Can I get a general consensus on that one? > > Peter > > > ------------------------------------------------------------------------------ > Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex > infrastructure or vast IT resources to deliver seamless, secure access to > virtual desktops. With this all-in-one solution, easily deploy virtual > desktops for less than the cost of PCs and save 60% on VDI infrastructure > costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox > _______________________________________________ > Tsheetx-developers mailing list > Tsh...@li... > https://lists.sourceforge.net/lists/listinfo/tsheetx-developers |
|
From: Peter L. <pal...@gm...> - 2012-01-05 22:22:41
|
Hi Dave, I have made the page access consistent, along with the new configuration table that Mark has created. Testing so far is good. I remember some bugs about who sees what, e.g. seeing another person's customers, or something like that. But that's not what this is about. It means for eample, that if the clients form has been set at "Manager" then lower levels cannot access it. This is somewhat achieved by the "Clients" menu item not being presented in the menu list. But the security tightening also prevents someone manually typing the url to get access to the clients page. I'm referring to the 2.0-demo branch. The 1.5.3 branch I haven't looked at for quite some time - wasn't that created for me to add a few new features, I've forgotten? If so I have not kept it up to date. All my work and Mark's work is in the 2.0-demo branch. In my email I had referred to 1.5.3 on the assumption we would release the demo branch as the next number in sequence 1.5.3. What state is the 2.0-demo branch at? I think it is almost ready for beta release. We need Mark to reorganise the install processes and a few other changes and it could be made ready. But Mark says that he will be busy until around Feb before he can start on that work. They're my two bob's worth. Mark may have a different view. Peter On 6 January 2012 07:41, David Thompson <tom...@us...>wrote: > Hi there, happy new year too. > > By all means change the defaults, but the testing of the different > settings is more important. I seem to remember some cases when I saw some > bugs, but I have never tested it rigorously enough. > > But when you say 1.5.3 do you mean the trunk version, or the 2.0-demo > branch? > What is the state of both? > > Cheers > > > Date: Wed, 4 Jan 2012 13:23:27 +1100 > > From: pal...@gm... > > To: tsh...@li... > > Subject: [Tsheetx-developers] Changing default permissions > > > > > Hello everyone, > > welcome to 2012. After not doing much on TSNG 1.5.3 for a couple of > > months, I hope to do some more useful development in January. > > > > One of the tasks I have reviewed and upgraded is to ensure the security > > permissions for various forms are consistent with their usage, and > > appropriate access level. For example, ensuring all forms to do with the > > definition and maintenance of clients, projects, tasks, rates use the > > appropriate aclClients, aclProjects etc. > > > > I would like to now change the default values in TSNG 1.5.3 for some of > > these permissions as follows, since the default is to set them all to > > "Basic". The access levels for clients, projects, tasks, rates, expense > > categories and timesheet approval should be defaulted to "Manager". This > > is because basic users should not be able, by default, to add and delete > > clients, projects, rates etc. > > > > Can I get a general consensus on that one? > > > > Peter > > > > > > > ------------------------------------------------------------------------------ > > Ridiculously easy VDI. With Citrix VDI-in-a-Box, you don't need a complex > > infrastructure or vast IT resources to deliver seamless, secure access to > > virtual desktops. With this all-in-one solution, easily deploy virtual > > desktops for less than the cost of PCs and save 60% on VDI > infrastructure > > costs. Try it free! http://p.sf.net/sfu/Citrix-VDIinabox > > _______________________________________________ > > Tsheetx-developers mailing list > > Tsh...@li... > > https://lists.sourceforge.net/lists/listinfo/tsheetx-developers > |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X