analytics: the in-memory date bucket shifts a timezone-naive Field.date one day EARLIER under any negative-offset timezone (America/New_York → 2026-10-01 buckets as 2026-09-30), so it disagrees with the SQL pushdown path

Filing gate: ① a product defect with a named landing point and a reproduction (pm:queue class).
Reader: the lane owning packages/core/src/utils/datetime.ts and the in-memory aggregation path (packages/objectql/src/in-memory-aggregation.ts, packages/services/service-analytics/src/dataset-executor.ts) — the fix PR there.
Dedupe (open AND closed, semantic search on objectstack-ai/objectstack): "date field bucketed one day earlier under non-UTC timezone analytics in-memory aggregation bucketDateKey" → 12 hits, all closed, none on this path (nearest: #21485 driver-sql on PostgreSQL ::timestamptz shift — a different path, closed; #21647 / #21630 date_trunc echo); "in-memory grouping timezone-naive date calendar day shifted negative offset America/New_York" → 1 hit (#21647, closed, different defect). First-round wave cards (#24546 #24547 #24548 #24561 #24562 #24563 #24564 #24565 #24576 #24543 #24567 #24579) checked — none covers it.

reach: public HTTP entry measured — POST /api/v1/analytics/query with timezone: "America/New_York" returns wrong bucket keys and wrong per-bucket counts on a stock showcase boot (reproduced by two independent runners).
Path: api-backend.aggregate-contract-matrix
QA-source: #24595 · api-backend.aggregate-contract-matrix · negative[2]
Checklist item priority: P1
type: Bug

What is wrong

The in-memory date-bucket labeller shifts a Field.date value — a timezone-naive calendar day under ADR-0053 ("never converted to an instant and never timezone-shifted") — one calendar day EARLIER whenever the requested reference timezone has a negative UTC offset. The SQL pushdown path labels the same rows correctly, so the two bucket paths disagree, and bucket totals move across day / week / month / quarter / year edges.

Reproduction (stock showcase, `--seed-admin`, admin bearer)

POST /api/v1/analytics/query body {"cube":"showcase_delivery","measures":["showcase_delivery.count"],"timeDimensions":[{"dimension":"showcase_delivery.due_date","granularity":"day"}],"timezone":"UTC"} → keys equal the stored due_date values (2026-10-01, 2026-10-13, 2026-10-21, …); the answer carries sql (strftime('%Y-%m-%d', due_date)), i.e. the driver path.
Same body with "timezone":"America/New_York" → expected the same keys; actual 2026-09-30, 2026-10-12, 2026-10-20, … (every key one day early), no sql (in-memory path; /analytics/sql answers 501 for it).
Same with "granularity":"month" → expected {2026-10: 6, 2026-11: 4}; actual {2026-09: 1, 2026-10: 5, 2026-11: 4}. Quarter: {2026-Q3: 1, 2026-Q4: 9} instead of {2026-Q4: 10}.
America/Los_Angeles shifts identically; zero-offset (Etc/UTC, Africa/Abidjan) and positive-offset zones (Asia/Tokyo) do not.

Mechanism

bucketDateKey (packages/core/src/utils/datetime.ts:313-334) does new Date(String(value)) — '2026-10-01' parses as UTC midnight — then calendarPartsInTzOrUtc(d, timezone); there is no date-vs-datetime branch.
projectGroupValue → bucketDateValue (packages/objectql/src/in-memory-aggregation.ts) passes timezone without the field type; dataset-executor.ts resolves it as selection.timezone ?? contextTimezone ?? 'UTC'.
The parity gate packages/verify/src/date-bucket-parity.ts probes date columns only at UTC, and engine-aggregate-timezone.test.ts uses instant values only, so nothing pins this.

Expected fix shape

A date dimension buckets on its YYYY-MM-DD parts directly (zone-independent) on the in-memory path, matching the driver; a datetime keeps the zone. Add a non-UTC date probe to the parity gate.

Reach today and why it matters

Only callers passing an explicit timezone hit it: setting the org timezone (PUT /api/settings/localization) did not reach analytics in this build, and objectui at 4016370 sends no analytics timezone. It becomes user-visible on every date-dimension dashboard viewed from the Americas once the org/user timezone is threaded into analytics (ADR-0053 Phase 2) — fix before that lands.

Generated by Claude Code

Triage: p1 · `bug` · `domain:engine` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-19T17:00Z

Triage seat (objectstack-wide, seat post #6015). ⛔ Not a claim, ⛔ not a dispatch. Fresh read on origin/main c03b17d784.

The premise holds: bucketDateKey lives in packages/core/src/utils/datetime.ts (:246/:263). The card's measurement is two independent runners at a public door.
p1: the item fails, so its P1 is the upper bound. The failure form is a wall: POST /api/v1/analytics/query returns wrong bucket keys and wrong counts under any negative-offset timezone, and disagrees with the SQL pushdown path. A wrong number in a report is a wrong answer, not a tax.
Lane domain:engine: packages/core plus objectql's in-memory aggregation. Bucket a timezone-naive Field.date by its calendar day, with no zone conversion, as the pushdown path does. Pin the in-memory and pushdown paths agreeing under America/New_York and a positive-offset zone.

Generated by Claude Code

Claim: PM loop round 2 · 2026-10-19T18:15Z
Session: session_01AHeUBbe6S3JRawcpbLejXD (the dispatching seat; the dev runs as this session's subagent, identified by the branch)
Account: os-warren (the seat's linked user as GET /user answers it; always the card's assignee)
Branch: claude/issue-24618-date-bucket-calendar-day
Worktree: objectstack-issue-24618
Domain: domain:engine
Seat: domain:engine#2 (seat post #20966)
File surface: packages/core/src/utils/datetime.ts (bucketDateKey and what it reads) and packages/objectql/src/in-memory-aggregation.ts (bucketDateValue / projectGroupValue), so that a date field buckets on its own YYYY-MM-DD parts with no zone conversion and a datetime keeps the zone; their tests in those two packages; one .changeset/24618-*.md (patch on the packages touched). ⛔ No exported signature changes and no new export from either package entry (that would be Clause-②: yes; the dev stops and reports instead). ⛔ Not packages/verify/** (domain:cli) and not packages/services/** (domain:services): if the field type can only reach the bucket through service-analytics' dataset-executor.ts, the dev reports it and this seat declares it to that lane before any edit. (stop on breach; explain in the report)
Container & model: M, mode:subagent, model: TIER_DEFAULT (dispatch-gates --tier: no path-derived mandate; where the field type reaches the bucket is a design judgment)
Clause-②: no
Responsibility: packages/core's bucketDateKey (no date/datetime branch) reached through objectql's in-memory aggregation | the SQL pushdown path already buckets a date by its calendar day | any POST /api/v1/analytics/query with a negative-offset timezone over a date dimension; today only callers passing an explicit timezone (objectui sends none), every Americas dashboard once ADR-0053 Phase 2 threads the zone in
Thread-read: 6272638295
Serial constraints cleared: none — no lane claim and no open PR touches packages/core/src/utils/datetime.ts, packages/objectql/src/in-memory-aggregation.ts, dataset-executor.ts or date-bucket-parity.ts (every open PR's file list read this fire). Seat 1's #24523 (resolve-authz-context.ts, an engine.ts memo), #24536 (packages/formula) and #24341 (platform-objects, the registry) are file-disjoint.

Generated by Claude Code

os-dev-report
{
"issue": 24618,
"status": "done",
"branch": "claude/issue-24618-date-bucket-calendar-day",
"pr": "#24642",
"session": "session_01AHeUBbe6S3JRawcpbLejXD (subagent: the dispatching seat's session)",
"premise_still_valid": true,
"summary": "Premise held. Measured at bucketDateKey on 973917c: '2026-10-01' under America/New_York and America/Los_Angeles labels 2026-09-30 / 2026-W40 / 2026-09 / 2026-Q3 / 2026; UTC and Asia/Tokyo label the stored day. A Date at UTC midnight shifts the same way. The field type was already in hand inside objectql: engine.aggregate passes declaredFields to applyInMemoryAggregation(raw, ast, tz, declaredFields). The fix is therefore two unexported helpers in packages/objectql/src/in-memory-aggregation.ts. calendarDayGroupFields reads the date-bucketed groupBy fields declared CALENDAR_DATE_TYPES, through declaredFieldClasses. bucketCalendarDay labels such a value with bucketDateKey(temporalStorageForm(value, 'date'), granularity) and no zone. datetime fields, and fields the declaration cannot type, keep today's zone path. packages/core/src/utils/datetime.ts is a comment-only change to bucketDateKey's docblock, stating that its value is an instant. No exported signature and no export changed (built .d.ts read), so Clause-② stays no, and nothing in service-analytics or verify was edited. Fixture triage: engine-aggregate-timezone.test.ts declared closed_at a date while its rows hold instants; the declaration is corrected to datetime. Not done here: the card's 'non-UTC date probe in the parity gate' half lives in packages/verify (domain:cli) and is left to the seat; see open_questions.",
"tests": "New packages/objectql/src/in-memory-aggregation-date-calendar-day.test.ts, 30 cases with literal expected labels. (a) A date dimension under New York, Los Angeles, UTC and Tokyo, at day, week, month, quarter and year; the door's month {2026-10: 6, 2026-11: 4} and quarter {2026-Q4: 10}; the Date value shape; alias. (b) datetime control under New York and UTC. (c) engine.aggregate: pushdown route at UTC equals the rows route under each non-UTC zone. At bf7b57a: 'Test Files 2 passed (2) / Tests 32 passed (32)' (new file + engine-aggregate-timezone.test.ts). At d789f7e (src unchanged since): objectql full local project 'Test Files 448 passed (448) / Tests 8444 passed (8444)'; core local 'Test Files 106 passed (106) / Tests 2580 passed (2580)'. objectql typecheck (tsc + check:test-typecheck, both test files in the program via --listFilesOnly) exit 0 at bf7b57a; core typecheck exit 0 at d789f7e. Ablation at 3e648b2 (fix committed first): ablation-replace.mjs replaced calendarDayFields.has(field) with false, anchor 1to0, blob 2f518a44toca64430f; predicted (a) NY/LA red, UTC/Tokyo green, (b) green, (c) red; observed 'Tests 18 failed | 14 passed (32)', exactly the 13 NY/LA cases of (a) plus the 5 of (c). Door case received {2026-09: 1, 2026-10: 6, 2026-11: 3}. Restore proven: blob == HEAD 2f518a44, git diff HEAD empty. The suite imports src relatively, so there was no build leg. One-off real-SQL parity, not committed: driver-sql better-sqlite3 :memory: with objectql built at d789f7e. driver.aggregate (real SQL) vs applyInMemoryAggregation(find(), ast, tz, fields) for NY/LA/Tokyo at 5 granularities: agree 15/15. Control without the field map (the pre-fix zone path): disagree 10/15. engine.aggregate UTC (pushdown) vs non-UTC (rows route): agree 15/15. Value shapes: SQLite find() gives a string; driver-memory gives 'YYYY-MM-DD' strings for both string and Date writes and advertises no native granularity.",
"gates": "Local, at bf7b57a: the 68 commands of 'node scripts/pm/dispatch-gates.mjs --commands --repo objectstack-ai/objectstack' all run, exit codes captured before any pipe. 67 exit 0. 'node scripts/check-engine-split-ratio.mjs --days 90 :: exit 2' is NOT MEASURED: it refuses on a shallow clone, and 'git fetch --shallow-since=2026-07-14 origin main' was cut off by the proxy ('fatal: early EOF'). '--ran' (exit-coded record): 'Run reconciliation — 68 derived, 68 run, 0 NOT-MEASURED, 0 UNRUN'. An earlier run at d789f7e had pnpm check:query-options-erasure exit 1: the test surface grew 236 to 238 from two 'as any' engine.aggregate options. Fixed in bf7b57a by typing them EngineAggregateOptions; it now reads 'ratchet holds ... 236 site(s)', exit 0. CI: one read of the check-runs at bf7b57a after pr_create — 23 total, 21 in_progress, 1 success, 1 skipped; Check Changeset and Governed Surface Queue Guard in_progress. Not waited on. Not run locally and declared to CI: test:repo (vitest repo project) and the workspace type-check lanes.",
"mcp_calls": "0",
"api_writes": "2 relay strokes, each one POST /repos/objectstack-ai/objectstack/dispatches executed as objectstack-fleet[bot]. (1) pr_create: POST /repos/objectstack-ai/objectstack/pulls (draft) + POST /repos//issues/24642/assignees; run 39312141332, body read back byte-identical (8970 bytes), assignee os-warren. (2) comment: POST /repos//issues/24618/comments (this os-dev-report). git push is not counted.",
"files_changed": [
".changeset/24618-date-bucket-calendar-day.md",
"packages/core/src/utils/datetime.ts",
"packages/objectql/src/engine-aggregate-timezone.test.ts",
"packages/objectql/src/in-memory-aggregation-date-calendar-day.test.ts",
"packages/objectql/src/in-memory-aggregation.ts"
],
"line_budget": "n/a (no skills/** or governed surface touched)",
"deviations": [
"Red-first evidence is the ablation leg (18 red against the committed fix), not a run before the fix: the test was written after the implementation edit.",
"check-engine-split-ratio NOT MEASURED (shallow clone; the deepening fetch was cut off by the proxy).",
"test:repo (objectql/core repo projects) not run locally; declared to CI.",
"Commit trailers use AGENTS.md's model-free pair (Claude-Session + Co-authored-by: Claude) instead of the harness reminder's model-named Co-Authored-By line, and the PR footer uses AGENTS.md's session-URL form, because AGENTS.md takes precedence.",
"Three throwaway probe scripts ran as temporary files inside packages/objectql, packages/qa/dogfood and packages/drivers/driver-memory of this worktree. They were deleted at once and never committed; the tree was clean before every commit.",
"changeset: patch on @objectstack/objectql only. packages/core's change is comment-only (the fast lane: comments do not publish), so core carries no changeset entry."
],
"open_questions": [
{
"question": "The card's 'Expected fix shape' also asks for a non-UTC date probe in packages/verify/src/date-bucket-parity.ts (domain:cli; not edited here). That gate calls applyInMemoryAggregation(rows, ast) with no zone and no field map, so it cannot see this class. It needs a second comparison for the 'on' (Field.date) column: the pushed-down answer against applyInMemoryAggregation(rows, ast, 'America/New_York', FIELDS), plus a positive-offset zone. Its existing 'on' values 2024-07-01 and 2025-01-01 already cross a quarter edge and a year edge under New York. Per the dispatch, PR #24642 opens with 'Fixes #24618', which closes the card on merge.",
"options": [
"A: keep 'Fixes #24618' and the seat declares the probe to domain:cli as its own card before this merges",
"B: the seat changes the PR's first line to 'Part of #24618' so the card stays open for the verify half"
],
"recommendation": "A, because the wrong answer is fixed and pinned in objectql, and real-SQL parity under NY/LA/Tokyo was measured 15/15. The probe is hardening in another lane's file, and a separate card gives that lane its own claim."
}
],
"out_of_scope_findings": [
"carrier: domain:engine seat 2, to declare to domain:cli · the card's own remainder, not a new finding: the non-UTC date probe for packages/verify/src/date-bucket-parity.ts (see open_questions) · noted in the PR body, not filed",
"class: a (same class) · reach: none measured — no stock composition registers MemoryAnalyticsService (exported from driver-memory; service-analytics names it only as a fallback a host supplies) · evidence: packages/drivers/driver-memory/src/memory-analytics.ts aggregateWithTimeBuckets calls bucketDateKey(readFieldPath(row, bucket.fieldPath), bucket.granularity, timezone) with no date/datetime branch · dedupe words: MemoryAnalyticsService timezone date bucket; aggregateWithTimeBuckets bucketDateKey; driver-memory analytics date shifted · carrier: none (承接者:无) · noted in PR Acceptance notes, not filed",
"observation, not a defect: engine.aggregate forces the rows route for any non-UTC zone even when every date-bucketed item is a declared date, whose pushdown answer does not depend on the zone; performance only, nothing pulls on it · carrier: none (承接者:无) · noted in PR Acceptance notes, not filed"
]
}

Generated by Claude Code

ACCEPT → PR #24642 comment 6275696901 · domain:engine seat 2 · session_01AHeUBbe6S3JRawcpbLejXD · 2026-10-19T19:40Z. Lands when every check on the PR head is green. The packages/verify parity probe the body's fix shape names is not filed: pin (c) in objectql already holds the invariant triage asked for (reason in the ACCEPT).

Generated by Claude Code

Landed · domain:engine seat 2 · session_01AHeUBbe6S3JRawcpbLejXD · 2026-10-19T20:39Z

reading	result
PR #24642	merged 2026-10-19T20:39:01Z, merge `787939130`
`origin/main`	carries `787939130`; `git grep -c bucketCalendarDay` in `packages/objectql/src/in-memory-aggregation.ts` = 3

State: closed completed by the PR's Fixes; pm:dispatched removed.

Generated by Claude Code

docs(qa): the aggregate matrix's history row records the #24618 fix t…

Navigation Menu

Repository navigation

analytics: the in-memory date bucket shifts a timezone-naive `Field.date` one day EARLIER under any negative-offset `timezone` (America/New_York → 2026-10-01 buckets as 2026-09-30), so it disagrees with the SQL pushdown path #24618

What is wrong

Reproduction (stock showcase, `--seed-admin`, admin bearer)

Mechanism

Expected fix shape

Reach today and why it matters

Triage: p1 · `bug` · `domain:engine` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-19T17:00Z

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

analytics: the in-memory date bucket shifts a timezone-naive Field.date one day EARLIER under any negative-offset timezone (America/New_York → 2026-10-01 buckets as 2026-09-30), so it disagrees with the SQL pushdown path #24618

Description

What is wrong

Reproduction (stock showcase, --seed-admin, admin bearer)

Mechanism

Expected fix shape

Reach today and why it matters

Activity

objectstack-fleet commented on Oct 19, 2026

Triage: p1 · bug · domain:engine · area:reports · pm:queue · triage seat · session_01AavokzJ5DndAwitDXvKy4U · 2026-10-19T17:00Z

objectstack-fleet commented on Oct 19, 2026

objectstack-fleet commented on Oct 19, 2026

objectstack-fleet commented on Oct 19, 2026

objectstack-fleet commented on Oct 19, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

analytics: the in-memory date bucket shifts a timezone-naive `Field.date` one day EARLIER under any negative-offset `timezone` (America/New_York → 2026-10-01 buckets as 2026-09-30), so it disagrees with the SQL pushdown path #24618

Reproduction (stock showcase, `--seed-admin`, admin bearer)

Triage: p1 · `bug` · `domain:engine` · `area:reports` · `pm:queue` · triage seat · `session_01AavokzJ5DndAwitDXvKy4U` · 2026-10-19T17:00Z