Accountlines Reports in 20.05

by Andrew Fuerste-Henry on Feb 23, 2021

The changes discussed in this post are the result of Bug 24818.

In the accountlines table, Koha maintains accountlines.date and accountlines.timestamp. The date field records when an entry was created, whereas the timestamp records when it was last changed. Prior to 20.05, the date field contains only a date. In 20.05 and later, however, this field will contain both the date and the time on which the entry was created.

If you have reports that use accountlines.date to filter results based on the date on which the accountline was created, you'll want to update your report to add the date() function to accountlines.date wherever you've used it in a WHERE statement.

I'll explain this in some more detail below, but if you're not sure what this means or if it applies to you, please open up a ticket and we'll be happy to help!

Dates and datetimes

On the Koha Reports Library page, you can find a section titled "How to avoid errors," which includes a subsection on dates and datetimes in BETWEEN statements. I'll be reiterating the main ideas from that subsection here.

To start, here are a couple of screenshots of the same very simple query run on a 19.11 site (which I'll call Train1 going forward) and then on a 20.05 site (which I'll call Train2 going forward).

Where 19.11 only shows a date, 20.05 includes a time.

Next, let's take a common example report. This one, written for 19.11, lists all the payments received yesterday:

The last line there is the important part. We're comparing the value from accountlines.date to a calculated date. Here's what that calculated date looks like:

There's nothing special there, we're just taking the current date and subtracting one day from it. The important part is that there's no time portion included here.

If I took a payment at 10:30 am yesterday on my 20.05 Train2 site, I'd expect it to show an accountlines.date value of "2021-02-21 10:30:00." If we run our example report against Train2, SQL will check to see if the value on my payment matches the calculated date from our query. In other words, it will check to see if this statement is true:

2021-02-21 10:30:00 = 2021-02-21

SQL will recognize that it's attempting to compare a datetime to date will attempt to correct that. However, the only way it knows to help is to append an assumed time of midnight (or 00:00 am, the very start of the day). At that point, SQL is evaluating whether or not this is true:

2021-02-21 10:30:00 = 2021-02-21 00:00:00

Which we know is not true, because 10:30 am is not the same as midnight. Because we know accountlines.date will always be a datetime and our report is only looking for a date, we've ended up with a report that accidentally only lists payments that happened exactly at midnight. Until we fix the report, we can expect it to always be empty.

What about BETWEENs?

This all gets a little bit trickier when we talk about date ranges. Here's our previous report, adapted to ask for a date range when we run it:

Those date pickers are going to give us dates without times. There is not a comparable datetime picker for use with reports, and we don't really want to specify times for this example, anyway.

If we imagine the same payment from 10:30 am yesterday, with our report set to check from 2/14/21 to 2/21/21, we end up with SQL evaluating whether or not this is true:

2021-02-21 10:30:00 IS BETWEEN 2021-02-14 AND 2021-02-21

And, as before, SQL realizes it's got a mix of dates and date times, so it inserts 00:00:00 as needed, giving us this:

2021-02-21 10:30:00 IS BETWEEN 2021-02-14 00:00:00 AND 2021-02-21 00:00:00

That makes the date range for our query midnight on 2/14 through midnight on 2/21, which is functionally the same as 2/14 - 2/20. Basically, we've accidentally chopped a day out of our range. Frustratingly, the report still gives results, just short a day, which makes it a little harder to notice the error.

How do we fix it?

Luckily, this is an easy fix. You just need to tell SQL to ignore the new time portion of accountlines.date. To do so, you can use the date() function to pull just the date out of our datetime. Here's our first report, adjusted accordingly:

And here's the second:

In both cases, we're simply sidestepping SQL's confusion about dates and datetimes by ignoring the time portion entirely.