A month-end filter can exclude almost all of the final day

A finance extract labeled April 1 through April 30 can omit transactions during April 30 if its upper bound is the start of that day. The visible date range may look correct while the underlying timestamp comparison asks a narrower question. Inspect the actual parameter types and values before assuming the source failed to supply a day's activity.

PostgreSQL defines BETWEEN as inclusive of both endpoints. It also documents that a date compared with a timestamp represents midnight, with the applicable time-zone interpretation. An inclusive comparison ending at midnight on April 30 includes that moment, not every later moment bearing the same calendar date. The word inclusive does not extend the endpoint to the end of the day.

Ask the analyst to show the field being filtered, its data type and the bound values after application conversion. A screen can submit a date that a query builder converts into a timestamp. Review that path directly rather than relying on a screenshot of the calendar picker or the report's title.

Construct a fictional single-zone example with a $100 event exactly at April 1 midnight, a $200 event exactly at April 30 midnight, a $300 event at noon on April 30 and a $400 event exactly at May 1 midnight. Assume the report is meant to cover the full April calendar month using that event field. The intended population contains the first three events, totaling $600.

A range ending inclusively at April 30 midnight admits the $100 and $200 events but omits the $300 noon event. It reports $300, leaving a $300 understatement in this small example. Extending the upper bound to May 1 midnight while keeping it inclusive admits the $400 next-period event as well, producing $1,000. Merely moving the endpoint does not settle the comparison operator.

Ask the developer to demonstrate the intended interval as at or after April 1 midnight and strictly before May 1 midnight, under the agreed time interpretation. That condition includes the three April events and excludes the May boundary event. It makes the period definition visible without inventing a final fractional second of April.

A patch that changes the endpoint to 23:59:59 may still mishandle events with fractional seconds if the stored timestamp supports them. Another patch that appends a sequence of nines depends on assumptions about precision and conversion. Prefer an explicitly reviewed next-period boundary over a value chosen because it looks like the end of the day on a screen.

Inspect whether the query casts the transaction timestamp to a date. That can express a different comparison and may have different execution consequences in the actual database. Ask the implementation owner to review both correctness and the report's normal performance. Do not replace one predicate with another across a large production table without understanding the supported access pattern.

Keep the date field's business meaning unchanged during the repair. Switching from payment time to invoice date might make the monthly total appear closer while changing the report's purpose. Correct the interval applied to the intended event, then investigate other genuine differences through their own evidence. A boundary defect should not become a quiet metric redesign.

The start of a calendar month depends on the reporting time zone when events are stored as instants. Have the data owner define that zone and construct both boundaries consistently. Do not assume a server's default zone matches the company's reporting convention. A correct pair of comparison operators can still select the wrong interval if the bounds represent different moments than finance intended.

PostgreSQL explains that comparisons between timestamp types can depend on its TimeZone setting, including the interpretation of a timestamp without time zone when compared with one that has a time zone. Ask the developer to make those conversions explicit in the report specification. Avoid mixing a date, a local timestamp and a UTC value without documenting how they become comparable.

For jurisdictions with daylight-saving changes, obtain the boundary conversion through the supported time-zone rules for the actual dates. Do not substitute a fixed offset copied from a different month. The test should record the original event values and the derived reporting bounds so a reviewer can reproduce the inclusion decision.

Run the corrected April and May extracts against the same stable source snapshot. A boundary event at the beginning of May should appear in May once and in April zero times under the stated calendar convention. Review both periods together; a fix that restores April's total by borrowing May's first transaction leaves a new error behind.

Include events immediately before and after each boundary at the precision the source actually uses. Test an empty period and a period whose last day differs in length from the previous month. Generate calendar boundaries through the supported date logic instead of assuming every month contains the same number of days.

Compare the old and corrected populations by stable identifiers, not only by total. Quantify the records excluded from the final day and any next-period records previously included. Keep late imports or subsequently edited events separate from the boundary correction by using the same extraction snapshot wherever possible. Otherwise the comparison can combine two different causes.

Update the report parameter handling and the query together where the defect spans both layers. Preserve a small boundary test with the agreed expected membership. The ordinary user should be able to select a month without knowing that an inclusive date label and a timestamp endpoint require different internal treatment.

Give the reporting owner the affected periods, corrected figures and event-level explanation. Decide which previously distributed reports require revision through the normal process. Keep the source transactions and their dates intact unless a separate accounting investigation establishes an error. The completed repair makes the monthly interval reproducible and includes every intended event exactly once.