Menu
▾
▴
Re: [Tsheetx-developers] Timezones, time adjustment and UTC
|
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>
|
