Hi Daniel, IMO the rule is defined by EntityDateFilterCondition#makeCondition(Timestamp, String, String) since this method is used throughout the framework to make date-based conditions. It builds the condition as "fromDate <= moment AND thruDate > moment", therefore rows where thruDate == moment would not appear in the resultset.
So it would seem that the demo data is incorrect to use x:59:59.999 timestamps in thruDates. Regards Scott On Tue, 31 Jan 2023 at 16:46, Daniel Watford <d...@foomoo.co.uk> wrote: > Hello, > > Although it may seem a minor topic, I would like to see if we can make a > statement about date ranges defined using fromDate and thruDate in OFBiz > entities. > > In The Data Resource Model Revised Edition volume 1 - Table 1.2 Conventions > Used In Attribute Naming, thru date is described as specifying the end date > of a date range and is inclusive of that date. > > The book draws a distinction between thruDate and toDate, stating that > thruDate is clearer for specifying the inclusive end of a date range. > > However I'm not sure if OFBiz is always using thruDate as an inclusive > value. > > Personally I'm not keen on using inclusive ranges for continuous > measurements - such as date times - since it bakes in an assumption on the > maximum precision of our unit of measure, as illustrated by this (very > contrived) example: > > *** BEGIN Dan's contrived example > > A self-service checkout allows customers to buy flour with the customer > pouring the flour onto the integrated weighing scales themselves. > > The pricing table for flour used by the self-checkout uses inclusive > ranges: > 0 to 5kg priced at $1.30 per kg. > 6kg to 10kg priced at $1.25 per kg. > 11kg or more priced at $1.20 per kg. > > Confusion occurs when figuring out what price we should use for 5.5kg of > the product. > > So we adjust the pricing table to: > 0 to 5.999kg priced at $1.30 per kg. > 6kg to 10.999kg priced at $1.25 per kg. > 11kg or more priced at $1.20 per kg. > > ... and can then answer that 5.5kg should be sold at $1.25 per kg. > > But then the weighing scales are upgraded to provide readings with more > significant digits and a customer (against the odds) pours 5.9995kg of > flour. We either need to get some rounding rules agreed or further enhance > the pricing table to allow for these new significant digits in our > measurements. > > Alternatively we could use exclusive 'to' values in our pricing table > > For weight W of flour: > 0kg <= W < 6kg: $1.30 per kg. > 6kg <= W < 11kg: $1.25 per kg. > 11kg <= W: $1.20 per kg. > > *** END Dan's contrived example > > So why am I asking about this? > > In service computeGlAccountBalanceForTimePeriod we select all transactions > that occurred BEFORE the time period we are interested in using the > condition: > transactionDate < customTimePeriod.fromDate > > The service then selects all transactions that occurred DURING the time > period we are interested in by using the condition: > transactionDate < customTimePeriod.thruDate > > The above condition suggests that computeGlAccountBalanceForTimePeriod has > been implemented with an assumption that the date range for a > CustomTimePeriod is EXCLUSIVE. > > If the date range for a custom time period is INCLUSIVE, the condition be: > transactionDate <= customTimePeriod.thruDate ? > > If we look at the Fiscal Year time period in the Demo Data ( > > https://demo-next.ofbiz.apache.org/accounting/control/TimePeriods?organizationPartyId=Company > ) > we see date: > fromDate: 2010-01-01 08:00:00.000 > thruDate: 2011-01-02 07:59:59.000 > > Ignoring the fact that the dates don't line up, the use of 07:59:59 > suggests the custom time periods have been defined using inclusive date > ranges. But given my contrived example above, shouldn't we be using > 07:59:59.999. > > I appreciate this all might be splitting hairs and that I am essentially > pointing at a 1 second gap for each financial period where some database > queries might miss out a transaction. And even if the transaction was > missed, it would be included in the query results the following time > period. But these gaps are where confusion can occur and subtle and > difficult to reproduce bugs can appear, so it's nice to get things fully > specified if possible. > > Therefore, do we have a general rule on whether thruDates in OFBiz are > inclusive or exclusive of the thruDate value itself? Or does the > inclusivity of the thruDate depend on context? > > Thanks for reading :) > > Dan. > > > -- > Daniel Watford >
