[Koha] SQL report

Tue Dec 16 10:41:02 NZDT 2008

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.

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.

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:

mysql> select "2007-12-06" < "2007-12-06 17:25:39" as lower_bound;
+-------------+
| lower_bound |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select "2007-12-06 17:25:39" < "2007-12-06" as upper_bound;
+-------------+
| upper_bound |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

NOTE: this applies only to the *statistics* 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 "2007-12-06" > "2007-12-06" would fail. 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.

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 "2007-12-06" to "2007-12-06" (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.

But hopefully that settles the question for date ranges against the
statistics table.

--Joe Atzberger

On Mon, Dec 15, 2008 at 7:33 AM, Nicole Engard <nicole.engard at liblime.com>wrote:

> >From my testing, I think it's
> > Nov 1 to Dec 1.  For a daily report it would be Dec 5 to Dec 6 to get
> stats
> > for Dec 5.
>
> This is right.  11/1 - 12/1 will give you the month of November.  Did
> I use the wrong word (inclusive v. exclusive)?
>
> ---
>
> Nicole C. Engard
> Open Source Evangelist, LibLime
> (888) Koha ILS (564-2457) ext. 714
> nce at liblime.com
> AIM/Y!/Skype: nengard
>
> http://liblime.com
> http://blogs.liblime.com/open-sesame/
>
>
>
> On Sun, Dec 14, 2008 at 9:40 AM, Sharon Moreland <smoreland at nekls.org>
> wrote:
> > Inclusive data for the month of November would be Nov 1 to Dec 1 or Oct
> 31
> > to Dec 1? The examples below are conflicting.  From my testing, I think
> it's
> > Nov 1 to Dec 1.  For a daily report it would be Dec 5 to Dec 6 to get
> stats
> > for Dec 5.
> >
> >
> > Sharon
> > NExpress Shared Catalog
> >
> > On Dec 12, 2008, at 5:57 AM, Nicole Engard wrote:
> >
> >> Last week I updated this document:
> >>
> >>
> http://sites.google.com/a/liblime.com/koha-manual/Home/Table-of-Contents/Reports/reports-wizards
> >> to include a tip about dates.
> >>
> >> "TIP: The statistic reports are exclusive, so, if you want a report to
> >> show data for the month of November you should pick October 31 to
> >> December 1."
> >>
> >> So if you want the month of December you should do December 1 to January
> >> 1.
> >>
> >>
> >> ---
> >>
> >> Nicole C. Engard
> >> Open Source Evangelist, LibLime
> >> (888) Koha ILS (564-2457) ext. 714
> >> nce at liblime.com
> >> AIM/Y!/Skype: nengard
> >>
> >> http://liblime.com
> >> http://blogs.liblime.com/open-sesame/
> >>
> >>
> >>
> >> On Mon, Dec 8, 2008 at 7:36 PM, Kathy Rippel <kdr at ckls.org> wrote:
> >>>
> >>> Josh-
> >>>
> >>> I tried this on Great Bend Public Library's collection and have a few
> >>> observations.
> >>>
> >>> If the to/from date is the same (12/08 - 12/08), the result is zero.
> >>>
> >>> So I tried 12/08 - 12/09 and then got a figure.
> >>>
> >>>
> >>> Kathy
> >>>
> >>>
> >>> At 02:47 AM 12/6/2008, Joshua Ferraro wrote:
> >>>>
> >>>> Hi Katharine,
> >>>>
> >>>> On Fri, Dec 5, 2008 at 6:22 PM, Katharine Dixon
> >>>> <katharin at ci.salinas.ca.us> wrote:
> >>>>>
> >>>>> Hello, I am new to the list so sorry if this has been asked before
> but
> >>>>> does
> >>>>> anybody have the SQL for a report that would just give me a total of
> >>>>> all
> >>>>> items circulated on a certain day.  Just the number, no titles or
> >>>>> types.
> >>>>
> >>>> You can find this out from the Circulation Statistics wizard, located
> >>>> in the Reports area.
> >>>> The path is /cgi-bin/koha/reports/issues_stats.pl
> >>>>
> >>>> Select Period as a row, and specify the dates in question as From/To.
> >>>>
> >>>> Select Library as a Column
> >>>>
> >>>> Run the report and you will get a chart of circulations per branch for
> >>>> that date range.
> >>>>
> >>>> Cheers,
> >>>>
> >>>> --
> >>>> Joshua Ferraro                       SUPPORT FOR OPEN-SOURCE SOFTWARE
> >>>> CEO                         migration, training, maintenance, support
> >>>> LibLime                                Featuring Koha Open-Source ILS
> >>>> jmf at liblime.com |Full Demos at http://liblime.com/koha |1(888)KohaILS
> >>>> _______________________________________________
> >>>> Koha mailing list
> >>>> Koha at lists.katipo.co.nz
> >>>> http://lists.katipo.co.nz/mailman/listinfo/koha
> >>>
> >>> Kathy Rippel
> >>> Dept. Head/Consultant--Resource Sharing and Access ;
> >>> Pathfinder Central (Manager)
> >>> Central Kansas Library System
> >>> 1409 Williams
> >>> Great Bend, KS 67530
> >>>
> >>> (620-792-4865) phone
> >>> (800-362-2642) toll-free, KS
> >>> (620-792-5495) fax
> >>>
> >>> kdr at ckls.org
> >>>
> >>>
> ********************************************************************************************
> >>> I'm currently reading: Redesigning the American Lawn, by F. Herbert
> >>> Bormann [... et al.]
> >>> I'm currently listening to: Xenocide, by Orson Scott Card.
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>> _______________________________________________
> >>> Koha mailing list
> >>> Koha at lists.katipo.co.nz
> >>> http://lists.katipo.co.nz/mailman/listinfo/koha
> >>>
> >> _______________________________________________
> >> Koha mailing list
> >> Koha at lists.katipo.co.nz
> >> http://lists.katipo.co.nz/mailman/listinfo/koha
> >
> >
> _______________________________________________
> Koha mailing list
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.katipo.co.nz/pipermail/koha/attachments/20081215/064cc956/attachment-0001.htm 