MotirBuilding in public
MOTIR · moooon
onMotir
You’re viewing a public project. Anyone can view it — no account needed. Sign in to submit, upvote, or comment on requests.View-only — you can’t edit work items
MOTIR-2056

Relative date filters (`in_last_days` / `in_next_days`) are off by one day whenever Postgres' session timezone is not UTC

Done
Description

Found while running the existing suite during Story 11.2 (MOTIR-2041). Pre-existing on origin/main @ c4ec51b1 — not caused by that story's diff (confirmed by stashing the diff and re-running: it fails identically). Logged rather than absorbed, per the drive-by-fix rule (notes.html #27).

The defect

The FilterAST date operators compile a comparison between a UTC calendar date and a session-local calendar date, so they disagree by one day for part of every day:

  • w."createdAt" / "updatedAt" / "dueDate" are timestamp WITHOUT time zone (verified against information_schema.columns), and Prisma writes UTC instants into them. column::date therefore yields the UTC calendar date.
  • CURRENT_DATE (compileConditionSql, lib/repositories/workItemRepository.ts — the in_last_days / in_next_days arms) is evaluated in the Postgres session timezone.

When the session timezone is not UTC, the two dates differ for the hours where local and UTC dates disagree. A probe through the real Prisma session:

tz           = America/Los_Angeles
CURRENT_DATE = 2026-08-03          <- session-local
createdAt    = 2026-08-04T00:55:31.966Z
createdAt::date = 2026-08-04       <- UTC
(createdAt::date >= CURRENT_DATE - 1 AND createdAt::date <= CURRENT_DATE) = false

So a row created one second ago does not match created in_last_days 1.

Impact

  • Every relative date filter on /items, on saved filters, and through search_work_items is off by one day for the affected window (7–8 hours a day at UTC−7/8; the whole of certain hours at any non-UTC offset).
  • It is invisible in CI, which runs with a UTC session — which is why it has never been caught. It is a real product defect on any deployment or developer machine whose Postgres session timezone is not UTC, not a test-only problem.
  • The absolute operators (on_or_before / on_or_after / between) compare a caller-supplied YYYY-MM-DD against column::date and are skewed by the same cast, though a user supplying a local date arguably expects local semantics — the fix should decide that deliberately rather than by accident.

Reproduction

With a Postgres whose session TimeZone is a negative UTC offset, at a wall-clock time after local midnight in UTC but before it locally:

pnpm vitest run tests/integration/work-items/filter-compiler.test.ts \
                tests/integration/work-items/filter-builder-matrix.test.ts

Two tests fail, both on the relative-window arm:

  • filter-compiler.test.ts → date operators: absolute, between, relative windows, empty (the created in_last_days 1 assertion — expects all four seeded rows, gets []).
  • filter-builder-matrix.test.ts → runs the full matrix as and as or.

Acceptance criteria

  • The relative window operators compare like with like: either evaluate the "today" boundary in UTC ((now() AT TIME ZONE 'UTC')::date) so both sides are UTC, or convert the column into the session zone — decided explicitly, with the choice and its user-facing meaning recorded in a comment.
  • A test pins the timezone rather than inheriting the server's: the suite sets a non-UTC session TimeZone and asserts the operators still select the right rows, so the bug cannot regress on a UTC-only CI. This is the load-bearing criterion — the current tests pass in CI because of the environment, which is what let this ship.
  • The same treatment is applied to the custom-field date arms (customFieldConditionSql), which carry the identical CURRENT_DATE comparison.
  • A decision is recorded for the ABSOLUTE operators (whether a YYYY-MM-DD from a client means a UTC day or a viewer-local day), even if the answer is "leave as-is".
  • grep shows no remaining comparison of a ::date-cast stored timestamp against a bare CURRENT_DATE.

Context refs

  • lib/repositories/workItemRepository.ts — compileConditionSql (built-in date arms) and customFieldConditionSql (the custom-field date arms); both use CURRENT_DATE.
  • lib/filters/registry.ts — dateField / DATE_WINDOW_OPERATORS, the operator set involved.
  • prisma/schema.prisma — the DateTime columns, which map to timestamp without time zone.
  • Related lesson: a test whose result depends on wall-clock/timezone rather than on the code under test.