Using the "Bookkeeping with extra fields" template, I noticed I was occasionally missing some transactions whenever I filtered on Transaction Date. I believe the issue is caused by the filter using only the date and not the time.
For example, if today is February 10 and I filter Transaction Date on "Yesterday", the WHERE clause looks like:
( financial_trxn.trxn_date >= '20190209') AND ( financial_trxn.trxn_date <= '20190209' )
Any transaction that isn't exactly at midnight is lost. Comparing to the Date Received filter, it uses date and time:
( contribution.receive_date >= '20190209000000') AND ( contribution.receive_date <= '20190209235959' )
Perhaps I'm misinterpreting the use of the filters. I can run the same query directly, but with the time included for Transaction Date, and it seems to return the correct results.
- CiviCRM 5.10.0
- PHP 7.2.13
- MySQL 5.7.23
- Wordpress 5.0.3
EDIT - originally this Q referenced ExtendedReports 3.5 but turns out it was a core report