<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">We have some reports that use DATE and others that use DATETIME, so this is useful.<div><br></div><div>Sharon</div><div><br><div><div>On Dec 15, 2008, at 3:41 PM, Joe Atzberger wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">I think the confusion is because it isn't clear where the inclusive or exclusive property applies. In this case, the query is both inclusive and exclusive, so I'll try to state the description somewhat technically. <br> <br>The question regards date ranges in reports, i.e. a range specified by a lower and upper bound. For statistical reports, the DATE is supplied by the user (like YYYY-MM-DD) but compared against a DATETIME field (like YYYY-MM-DD HH:mm:ss). Thus, the behavior is that the lower bound is inclusive, because even the first second of a day sorts to *after* the DATE string by itself. The same principle applies to exclude the upper bound. <br> <br>Here's how mysql treats this problem, in concrete terms. I'm using the DATE "2007-12-06" against the DATETIME "2007-12-06 17:25:39". Examples:<br><br><span style="font-family: courier new,monospace;">mysql> select "2007-12-06" < "2007-12-06 17:25:39" as lower_bound;</span><br style="font-family: courier new,monospace;"> <span style="font-family: courier new,monospace;"> +-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> | lower_bound |</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> +-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> | 1 | </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> +-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> 1 row in set (0.00 sec)</span><br style="font-family: courier new,monospace;"><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">mysql> select "2007-12-06 17:25:39" < "2007-12-06" as upper_bound;</span><br style="font-family: courier new,monospace;"> <span style="font-family: courier new,monospace;">+-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| upper_bound |</span><br style="font-family: courier new,monospace;"> <span style="font-family: courier new,monospace;">+-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">| 0 | </span><br style="font-family: courier new,monospace;"> <span style="font-family: courier new,monospace;">+-------------+</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">1 row in set (0.00 sec)</span><br style="font-family: courier new,monospace;"> <span style="font-family: courier new,monospace;"></span><br style="font-family: courier new,monospace;"><br>NOTE: this applies only to the <b>statistics</b> table and other places where we compare DATE vs DATETIME (or TIMESTAMP). When querying *other* tables and the comparison is DATE vs. DATE, you would expect different behavior. Of course <span style="font-family: courier new,monospace;">"2007-12-06" > "<span style="font-family: courier new,monospace;">2007-12-06</span><span style="font-family: verdana,sans-serif;"><span style="font-family: courier new,monospace;">"</span> would fail.</span></span><span style="font-family: courier new,monospace;"> </span>In date vs. date, the comparisons *should* be <= and >=, but I cannot say that they are. A lot of the underlying report code has been copied around and I would expect the authors did not adjust accordingly for different behavior that corresponds to different data types. <br> <br>I think Koha *should* evolve to the point where the date specification is the same regardless of the type of report. Probably both ends should be inclusive, such that the date range <span style="font-family: courier new,monospace;">"<span style="font-family: courier new,monospace;">2007-12-06</span><span style="font-family: verdana,sans-serif;"><span style="font-family: courier new,monospace;">" to </span></span></span><span style="font-family: courier new,monospace;">"<span style="font-family: courier new,monospace;">2007-12-06</span><span style="font-family: verdana,sans-serif;"><span style="font-family: courier new,monospace;">"</span></span></span> (the same day) would result in a one day range. Currently (for statistics), it results in an empty set. This sounds easy but it gets complicated when you get into the upcoming hourly circulation features that will change a lot of the fields in the DB to be fuller types, and the fact that the guided reports writer targets different tables. <br> <br>But hopefully that settles the question for date ranges against the statistics table.<br><br>--Joe Atzberger<br><br><br><div class="gmail_quote">On Mon, Dec 15, 2008 at 7:33 AM, Nicole Engard <span dir="ltr"><<a href="mailto:nicole.engard@liblime.com">nicole.engard@liblime.com</a>></span> wrote:<br> <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div class="Ih2E3d">>From my testing, I think it's<br> > Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats<br> > for Dec 5.<br> <br> </div>This is right. 11/1 - 12/1 will give you the month of November. Did<br> I use the wrong word (inclusive v. exclusive)?<br> <div class="Ih2E3d"><br> ---<br> <br> Nicole C. Engard<br> Open Source Evangelist, LibLime<br> (888) Koha ILS (564-2457) ext. 714<br> <a href="mailto:nce@liblime.com">nce@liblime.com</a><br> AIM/Y!/Skype: nengard<br> <br> <a href="http://liblime.com" target="_blank">http://liblime.com</a><br> <a href="http://blogs.liblime.com/open-sesame/" target="_blank">http://blogs.liblime.com/open-sesame/</a><br> <br> <br> <br> </div><div><div></div><div class="Wj3C7c">On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <<a href="mailto:smoreland@nekls.org">smoreland@nekls.org</a>> wrote:<br> > Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct 31<br> > to Dec 1? The examples below are conflicting. From my testing, I think it's<br> > Nov 1 to Dec 1. For a daily report it would be Dec 5 to Dec 6 to get stats<br> > for Dec 5.<br> ><br> ><br> > Sharon<br> > NExpress Shared Catalog<br> ><br> > On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:<br> ><br> >> Last week I updated this document:<br> >><br> >> <a href="http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Reports/reports-wizards" target="_blank">http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Reports/reports-wizards</a><br> >> to include a tip about dates.<br> >><br> >> "TIP: The statistic reports are exclusive, so, if you want a report to<br> >> show data for the month of November you should pick October 31 to<br> >> December 1."<br> >><br> >> So if you want the month of December you should do December 1 to January<br> >> 1.<br> >><br> >><br> >> ---<br> >><br> >> Nicole C. Engard<br> >> Open Source Evangelist, LibLime<br> >> (888) Koha ILS (564-2457) ext. 714<br> >> <a href="mailto:nce@liblime.com">nce@liblime.com</a><br> >> AIM/Y!/Skype: nengard<br> >><br> >> <a href="http://liblime.com" target="_blank">http://liblime.com</a><br> >> <a href="http://blogs.liblime.com/open-sesame/" target="_blank">http://blogs.liblime.com/open-sesame/</a><br> >><br> >><br> >><br> >> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <<a href="mailto:kdr@ckls.org">kdr@ckls.org</a>> wrote:<br> >>><br> >>> Josh-<br> >>><br> >>> I tried this on Great Bend Public Library's collection and have a few<br> >>> observations.<br> >>><br> >>> If the to/from date is the same (12/08 - 12/08), the result is zero.<br> >>><br> >>> So I tried 12/08 - 12/09 and then got a figure.<br> >>><br> >>><br> >>> Kathy<br> >>><br> >>><br> >>> At 02:47 AM 12/6/2008, Joshua Ferraro wrote:<br> >>>><br> >>>> Hi Katharine,<br> >>>><br> >>>> On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon<br> >>>> <<a href="mailto:katharin@ci.salinas.ca.us">katharin@ci.salinas.ca.us</a>> wrote:<br> >>>>><br> >>>>> Hello, I am new to the list so sorry if this has been asked before but<br> >>>>> does<br> >>>>> anybody have the SQL for a report that would just give me a total of<br> >>>>> all<br> >>>>> items circulated on a certain day. Just the number, no titles or<br> >>>>> types.<br> >>>><br> >>>> You can find this out from the Circulation Statistics wizard, located<br> >>>> in the Reports area.<br> >>>> The path is /cgi-bin/koha/reports/issues_stats.pl<br> >>>><br> >>>> Select Period as a row, and specify the dates in question as From/To.<br> >>>><br> >>>> Select Library as a Column<br> >>>><br> >>>> Run the report and you will get a chart of circulations per branch for<br> >>>> that date range.<br> >>>><br> >>>> Cheers,<br> >>>><br> >>>> --<br> >>>> Joshua Ferraro SUPPORT FOR OPEN-SOURCE SOFTWARE<br> >>>> CEO migration, training, maintenance, support<br> >>>> LibLime Featuring Koha Open-Source ILS<br> >>>> <a href="mailto:jmf@liblime.com">jmf@liblime.com</a> |Full Demos at <a href="http://liblime.com/koha" target="_blank">http://liblime.com/koha</a> |1(888)KohaILS<br> >>>> _______________________________________________<br> >>>> Koha mailing list<br> >>>> <a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br> >>>> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br> >>><br> >>> Kathy Rippel<br> >>> Dept. Head/Consultant--Resource Sharing and Access ;<br> >>> Pathfinder Central (Manager)<br> >>> Central Kansas Library System<br> >>> 1409 Williams<br> >>> Great Bend, KS 67530<br> >>><br> >>> (620-792-4865) phone<br> >>> (800-362-2642) toll-free, KS<br> >>> (620-792-5495) fax<br> >>><br> >>> <a href="mailto:kdr@ckls.org">kdr@ckls.org</a><br> >>><br> >>> ********************************************************************************************<br> >>> I'm currently reading: Redesigning the American Lawn, by F. Herbert<br> >>> Bormann [... et al.]<br> >>> I'm currently listening to: Xenocide, by Orson Scott Card.<br> >>><br> >>><br> >>><br> >>><br> >>><br> >>><br> >>> _______________________________________________<br> >>> Koha mailing list<br> >>> <a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br> >>> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br> >>><br> >> _______________________________________________<br> >> Koha mailing list<br> >> <a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br> >> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br> ><br> ><br> _______________________________________________<br> Koha mailing list<br> <a href="mailto:Koha@lists.katipo.co.nz">Koha@lists.katipo.co.nz</a><br> <a href="http://lists.katipo.co.nz/mailman/listinfo/koha" target="_blank">http://lists.katipo.co.nz/mailman/listinfo/koha</a><br> </div></div></blockquote></div><br></blockquote></div><br></div></body></html>