Sub-hour timezone offsets (+05:30, +05:45, +10:30) make every analytics card fail

[cosmosuite]

What happens

Self-hosted, Asia/Kolkata (+05:30). Every card on the dashboard returns:

Range not available — This range and timezone cannot be served at this grain. Try a shorter range or UTC.

Overview totals, all six top-N reports, web vitals, bounce rate, average visit, revenue, and the chart at anything past the minute tier. Only realtime and the "Today" chart survive, because those use metrics_1m.

Ingest is fine — events_raw fills, rollups aggregate, first_event_at is stamped. It is purely the read path.

Why

packages/domain/src/analytics-query.ts classifies the zone and refuses hour/day/week for sub-hour alignment. The module header explains it well and the reasoning is correct: every rollup buckets on UTC boundaries, a +05:30 local midnight falls inside a *_1h bucket, and an aggregate cannot be split. Returning misattributed buckets would be worse than refusing.

The problem is the resulting experience, not the logic:

1. apps/web/components/ui/timezone-select.tsx offers every IANA zone — filterZones is a text search with no alignment filter.
2. apps/web/app/docs/timezones/page.tsx doesn't mention the restriction.
3. The error says "Try a shorter range or UTC", which reads as transient rather than "this timezone is structurally unsupported".

So a new self-hoster in Bangalore installs, adds a site, sees every card fail, and concludes the software is broken. That failure doesn't arrive as an issue — it arrives as an uninstall.

Scale

This is every zone with a non-whole-hour offset: India and Sri Lanka (+05:30), Nepal (+05:45), Iran (+03:30), Afghanistan (+04:30), Myanmar (+06:30), Adelaide/Darwin (+09:30), Lord Howe (+10:30), Chatham (+12:45), Newfoundland (−03:30), Marquesas (−09:30). On the order of 1.6 billion people, and weighted toward a region with a very large self-hosting developer population.

It triggers on the viewer's zone, so it hits regardless of where a site's traffic comes from.

I have a working branch

https://github.com/cosmosuite/openanalytics/tree/subhour-timezones — rebased onto main, pnpm run verify green (3001 tests), deployed and verified against live data.

It uses three sources, chosen so no rule is stated twice:

• events_raw for the additive family. toStartOf*(occurred_at, {tz:String}) is exact for any whole-minute offset. New MAX_SPAN_RAW_DAYS (92d) cap, site-scoped, row-capped.
• session_facts_versions for bounce rate and average visit — the provisional layer already read this table with a timezone-aware bucket; only alignment: 'hour' refused the range.
• revenue_1m (new migration 0022) for revenue, because the finalizer already computes buckets per unit. Adding '1m' to UNITS writes it in the same pass under the same claim-minted generation, so it cannot disagree with revenue_1h. No money rule is restated.

Verified against real data: raw vs rollup over the same UTC window agree exactly on events, pageviews and visitors; session facts vs session_rollups_1h agree on sessions, bounces, pageviews and duration; and the local buckets land on :30, as +05:30 requires.

The question, before I open a PR

CONTRIBUTING.md asks for an issue first on the query gateway, and this is the reason:

Is a bounded events_raw read an acceptable exception to §15's "the additive family must read a rollup only", or would you rather have a locally-bucketed rollup family?

§15 names overview explicitly, so I did not want to assume. My reading is that the rule guards against a dashboard card becoming an unbounded scan of the event table, and that the registry's existing exemptions (funnel_session, funnel_visitor, recent_visitors, visitor_trail, visitor_revenue_entries) set the terms: site-scoped, span-capped, row-capped. The raw operations meet all three. But a locally-bucketed rollup family would be a better long-term answer and a much larger change, and that call is yours.

Two smaller things I would fix before submitting, flagged for completeness:

• The three raw chart operations drop the imported-data branch. importedBucketExpr places a provider day at its local midnight, and I did not want to guess at that placement for a zone whose local midnight is mid-hour. A sub-hour site with a published import would see live data only.
• MAX_SPAN_RAW_DAYS = 92 is a judgement call (it covers today/7d/30d/90d and refuses 6mo/1y/all). Might belong in config rather than as a constant.

Happy to shape it whichever way you prefer, or to hand the branch over if you would rather take it from here.

Environment

• Self-hosted infra/selfhost, ClickHouse 26.3.17.56, Postgres 17
• Reproduced on v0.1.0 and rebased onto main (0.3.2)
• Browser timezone Asia/Kolkata, site reporting_timezone unset

Separately, #1 adds the settings field for reporting_timezone — independent of this, and mergeable on its own.