<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Wed, May 4, 2016 at 11:15 PM, Mark Tompsett <span dir="ltr"><<a href="mailto:mtompset@hotmail.com" target="_blank">mtompset@hotmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div lang="EN-AU" dir="ltr" vlink="purple" link="blue">
<div dir="ltr">
<div style="font-size:12pt;font-family:Calibri;color:rgb(0,0,0)">
<div>Greetings,</div>
<div> </div>
<div>Sorry if I took something out of context to create a different thread.</div></div></div></div></blockquote><div><br></div><div>I think that I may have highjacked the original thread. Apologies to David Cook and Larry Letelier, and thank you for creating a thread specific to this conversation.</div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div lang="EN-AU" dir="ltr" vlink="purple" link="blue"><div dir="ltr"><div style="font-size:12pt;font-family:Calibri;color:rgb(0,0,0)">
<div> </div>
<div>David Cook wrote:</div>
<div><font style="font-size:11pt">> “Maybe Koha is fine just being a 1 timezone system.”</font></div>
<div> </div>
<div>What I would prefer is a 1 timezone backend, with user specific timezone front-end with a default timezone.</div>
<div>Why? Global consortia. The organization I work with has libraries across... 1,2,3, perhaps 4 time zones maybe 5.</div>
<div>I could easily imagine it increasing in the future.</div></div></div></div></blockquote><div><br></div><div>Instances with branches that span time zones is certainly not the most common case, but it's an exception that's likely to bite someone in the hind quarters. "Do you have branches in separate time zones" is not a question that's likely to be asked in a pre-migration interview, and could very possibly be a show-stopper. I could easily see this causing a lot of wasted time, money and effort.</div><div><br></div><div>Koha is supposed to be a general purpose ILS, and I don't think that limiting it to a </div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div lang="EN-AU" dir="ltr" vlink="purple" link="blue"><div dir="ltr"><div style="font-size:12pt;font-family:Calibri;color:rgb(0,0,0)">
<div> </div>
<div>GPML,</div>
<div>Mark Tompsett</div>
<div> </div>
<div>P.S. Back to your regularly scheduled Y2038 doomsday discussion.</div>
<div style="font-size:small;text-decoration:none;font-family:Calibri;font-weight:normal;color:rgb(0,0,0);font-style:normal;display:inline">
<div style="font-style:normal;font-variant:normal;font-weight:normal;font-stretch:normal;font-size:10pt;line-height:normal;font-family:tahoma">
<div> </div>
<div style="background:rgb(245,245,245)">
<div><b>From:</b> <a title="dcook@prosentient.com.au" href="mailto:dcook@prosentient.com.au" target="_blank">
David Cook</a> </div>
<div><b>Sent:</b> Wednesday, May 04, 2016 8:23 PM</div>
<div><b>To:</b> <a title="barton@bywatersolutions.com" href="mailto:barton@bywatersolutions.com" target="_blank">
'Barton Chittenden'</a> </div>
<div><b>Cc:</b> <a title="koha-devel@lists.koha-community.org" href="mailto:koha-devel@lists.koha-community.org" target="_blank">
koha-devel@lists.koha-community.org</a> </div>
<div><b>Subject:</b> Re: [Koha-devel] Invalid local time for date in time zone:America/Santiago</div>
</div>
</div>
<div> </div>
</div>
<div style="font-size:small;text-decoration:none;font-family:Calibri;font-weight:normal;color:rgb(0,0,0);font-style:normal;display:inline">
<div>
<p class="MsoNormal"><a name="m_9141167478667857119__MailEndCompose"><span style="font-size:11pt;font-family:Calibri,sans-serif">Hey Barton,<u></u><u></u></span></a></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Yes, the year 2038 for timestamps is a problem even on 64 bit machines.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Here’s a test you can do on a 64 bit MySQL install on a 64 bit OS:<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">select unix_timestamp('2038-01-20');<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">select from_unixtime<a href="tel:%282548990800" value="+12548990800" target="_blank">(2548990800</a>);<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Both will return null.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">You can also try storing a value of ‘2039-01-01’ into a timestamp column, and you’ll get the following message :<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Out of range value for column 'timestamp' at row 1<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Here’s some good explanations:<u></u><u></u></span></p>
<p class="MsoNormal"><a href="http://askubuntu.com/questions/675116/timestamp-year-2038-problem-for-64-bit-ubuntu-system" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">http://askubuntu.com/questions/675116/timestamp-year-2038-problem-for-64-bit-ubuntu-system</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span></p>
<p class="MsoNormal"><a href="https://bugs.mysql.com/bug.php?id=12654" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">https://bugs.mysql.com/bug.php?id=12654</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Also, I’d never heard of date_t before your email, and the only references I can see to it are for specific projects which have implemented it
as a new data structure themselves. I think time_t is the standard and that depends on your system architecture, although as you can see in those above links – it’s more complicated than that in MySQL.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">--<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">issues.date_due is already a DATETIME data type, so we don’t need to worry about 2038. As for time zones, why would it be tricky storing it in
UTC? <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">At the moment, you can only have due dates in one time zone. That means you can’t have library systems spanning more than one timezone. Maybe
that’s a rare or uncommon scenario, but here in the state of New South Wales, we actually do have 2 different timezones. In that case, when you’re doing a check out, you choose a local time, convert it to UTC for storage, and then render it in local time for
the library user and library staff. <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">I imagine the trickiest part would be the Reports module, as users wouldn’t know to use something like the CONVERT_TZ() function.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Calculating overdues against holidays might be tricky… although that’s a case of just getting your timezone right as well. I reckon your timezone
should be set per library, so you just fetch your UTC datetime, convert it to your local library timezone, probably convert it to floating to do math, and then convert it back to local timezone, and then whatever else you need. It sounds complex, but it’s
really not that bad when you have it structured well.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">--<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Barton, I’m not sure I understand your reaction :p. I also field support tickets for a living, and I think it would be a lot easier to field tickets
for a re-architected system that is created off years of experience rather than years of technical debt. Of course, I know re-architecting a system is also a lot easier said than done. But I think that’s why you see folks like EBSCO thinking about creating
a microservice based system which can integrate with Koha. Incremental change.<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">I suppose dates are most important when it comes to circulation and patrons… and I think that’s what people probably think about when they think
of core Koha. I suppose acquisitions and serials require a lot of careful date handling as well… I was actually thinking about Koha and “asset tracking” in general this morning on the way to the office.
<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">I don’t know. Maybe Koha is fine just being a 1 timezone system. I imagine library systems spanning over multiple timezones are probably the exception
rather than the rule. <u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">We need to re-think some of our DateTime logic though. Whether we use ‘floating’ timezones more consistently, or are careful with creating local
times that don’t exist…<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">David Cook<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Systems Librarian<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Prosentient Systems<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">72/330 Wattle St<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Ultimo, NSW 2007<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Office: 02 9212 0899<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif">Direct: 02 8005 0595<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif"><u></u><u></u></span> </p>
<div style="border-width:medium medium medium 1.5pt;border-style:none none none solid;padding:0cm 0cm 0cm 4pt;border-left-color:blue">
<div>
<div style="border-top-color:rgb(225,225,225);border-width:1pt medium medium;border-style:solid none none;padding:3pt 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif">From:</span></b><span lang="EN-US" style="font-size:11pt;font-family:Calibri,sans-serif"> Barton Chittenden [mailto:<a href="mailto:barton@bywatersolutions.com" target="_blank">barton@bywatersolutions.com</a>]
<br>
<b>Sent:</b> Thursday, 5 May 2016 12:04 AM<br>
<b>To:</b> David Cook <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>><br>
<b>Subject:</b> Re: [Koha-devel] Invalid local time for date in time zone: America/Santiago<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><u></u><u></u> </p>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
<div>
<p class="MsoNormal">On Thu, Apr 28, 2016 at 7:27 PM, David Cook <<a href="mailto:dcook@prosentient.com.au" target="_blank">dcook@prosentient.com.au</a>> wrote:<u></u><u></u></p>
<blockquote style="border-width:medium medium medium 1pt;border-style:none none none solid;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;border-left-color:rgb(204,204,204);margin-right:0cm">
<div>
<div>
<p class="MsoNormal">
<a name="m_9141167478667857119_m_-348189150733400176__MailEndCompose"><span style="font-size:11pt;font-family:Calibri,sans-serif">I don’t think using “timestamp” would be preferable to “datetime”.
</span></a><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif">First, MySQL’s timestamp seems to suffer from the Year 2038 problem (</span><a href="https://en.wikipedia.org/wiki/Year_2038_problem" target="_blank"><span style="font-size:11pt;font-family:Calibri,sans-serif">https://en.wikipedia.org/wiki/Year_2038_problem</span></a><span style="font-size:11pt;font-family:Calibri,sans-serif">)(<a href="http://bugs.mysql.com/bug.php?id=12654" target="_blank">http://bugs.mysql.com/bug.php?id=12654</a>),
while datetime does not.</span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><u></u><u></u></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">Even on 64 bit machines?<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">Oh MySQL, MySQL, knowest thou not of date_t?<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
<blockquote style="border-width:medium medium medium 1pt;border-style:none none none solid;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;border-left-color:rgb(204,204,204);margin-right:0cm">
<div>
<div>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif">Second, you’d be handling timezones at the data level rather than at the application level. I’d rather have a convention of storing DATETIME as UTC, having Koha fetch that DATETIME as UTC, and
then doing timezone conversions from there. </span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><u></u><u></u></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">I think that could be handled *relatively* transparently in DateUtils.pm, but there are a lot of corner cases like due dates that get ... tricky.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<blockquote style="border-width:medium medium medium 1pt;border-style:none none none solid;padding:0cm 0cm 0cm 6pt;margin-left:4.8pt;border-left-color:rgb(204,204,204);margin-right:0cm">
<div>
<div>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif">As for the same server running different time zones, you can change the timezone on a per connection setting I think, but that could be a hassle. I think it would be easier just to store things
clearly as UTC. This is done on a different project I work on, and it works well.</span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif">Oh, I think it’ll be a nightmare changing timezone/timestamp/datetime handling in Koha. I don’t even know if it’s possible, to be honest. Well, it would be possible, but it would likely be an
error-prone process. </span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif"> </span><u></u><u></u></p>
<p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif">It’s things like this which make me think that we need to start thinking about a version of Koha that doesn’t have a clear upgrade path. A Koha with some major architectural changes which will
need a skilled hand to migrate to. It won’t be nice for a lot of people… but I don’t know how else we’re going to make some major changes without dramas.</span><u></u><u></u></p>
</div>
</div>
</blockquote>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">From a design standpoint, I agree.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">From the point of view of someone who fields support tickets for a living:<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">NOOOOOOOOOOOOOOOOOOOOO!!!!<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">... but that's just an emotional response.<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">Cheers,<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"><u></u><u></u> </p>
</div>
<div>
<p class="MsoNormal">--Barton<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> <u></u><u></u></p>
</div>
</div>
</div>
</div>
</div>
</div>
<p></p>
<hr>
_______________________________________________<br>
Koha-devel mailing list<br>
<a href="mailto:Koha-devel@lists.koha-community.org" target="_blank">Koha-devel@lists.koha-community.org</a><br>
<a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>
website : <a href="http://www.koha-community.org/" target="_blank">http://www.koha-community.org/</a><br>
git : <a href="http://git.koha-community.org/" target="_blank">http://git.koha-community.org/</a><br>
bugs : <a href="http://bugs.koha-community.org/" target="_blank">http://bugs.koha-community.org/</a></div>
</div>
</div>
</div>
<br>_______________________________________________<br>
Koha-devel mailing list<br>
<a href="mailto:Koha-devel@lists.koha-community.org">Koha-devel@lists.koha-community.org</a><br>
<a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" rel="noreferrer" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>
website : <a href="http://www.koha-community.org/" rel="noreferrer" target="_blank">http://www.koha-community.org/</a><br>
git : <a href="http://git.koha-community.org/" rel="noreferrer" target="_blank">http://git.koha-community.org/</a><br>
bugs : <a href="http://bugs.koha-community.org/" rel="noreferrer" target="_blank">http://bugs.koha-community.org/</a><br></blockquote></div><br></div></div>