Actually it is slightly trickier than that with the statistics table. You are passing it a DATE value (YYYY-MM-DD) and the "datetime" field is (reasonably enough) a DATETIME field (YYYY-MM-DD hh:mm::ss). So this has an interesting implication: it matches on comparison for lower bound only. The reason is that on the same date, ANY datetime sorts to after the date value with NO time. mysql> select "2007-12-06 17:25:39" < "2007-12-06"; +--------------------------------------+ | "2007-12-06 17:25:39" < "2007-12-06" | +--------------------------------------+ | 0 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select "2007-12-06 17:25:39" > "2007-12-06"; +--------------------------------------+ | "2007-12-06 17:25:39" > "2007-12-06" | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec) In my opinion, this is actually one version of what users would want, so they can put 9/1-10/1 and don't have to remember how many days September had. Note that this is data dependent, since the other report interfaces that query different tables (with regular date fields) will not behave this way on their own. This other behavior is probably what you were thinking of, Owen. The alternative, probably more correct approach would be to make both dates inclusive, such that you could limit to one day by specifying it as both lower and upper bound. This would require you to know how many days September had, but since we have a Calendar picker... it can remember for you. In the end, I don't think users care too much what logic is applied as long as it is documented and they can make efficient use of it. --Joe On Sat, Dec 6, 2008 at 10:35 AM, Owen Leonard <oleonard@myacpl.org> wrote:
SELECT count(*) FROM statistics WHERE type = 'issue' AND datetime > '2008-04-30' AND datetime < '2008-05-01'
Comparing this method with Josh's instructions for running the report in Koha:
Koha reports 40705 circulations for all branches during the month of July 2008.
I ran this query in mysql:
select count(*) from statistics WHERE type = 'issue' AND datetime > '2008-06-30' AND datetime < '2008-08-01';
...and got 44227. It looks like the reports interface in Koha is using the same logic as the SQL statement, so if you choose "Jul 1" through "Jul 31" on the date-pickers the report will not include circulations from those dates. Selecting "June 30" and "Aug. 1" as the start and end dates produces results that match the SQL.
-- Owen -- Web Developer Athens County Public Libraries http://www.myacpl.org