Summary

Every chart with a date dimension returns 500 for users whose Atlassian profile time zone is a legacy IANA alias such as Asia/Calcutta. Production Postgres rejects those names:

ERROR: time zone "Asia/Calcutta" not recognized

This affects dashboard gadgets, the gadget editor preview and the Overview page, for every user on an affected zone, permanently. Asia/Calcutta is the common case, so in practice whole tenants in India see no working charts. Chart requests now resolve the requested zone to a name the database actually accepts before any SQL is built.

Root cause

The tz request parameter carries the Atlassian user profile time zone (AP.user.getTimeZone on Connect, context.timezone on Forge). Jira's picker still offers tzdata backward aliases, and ExportUtil.getZoneId validates the value only against Java's time zone database, which keeps those aliases and never canonicalises them: ZoneId.of("Asia/Calcutta").getId() returns Asia/Calcutta.

ChartDimensionSql.dateBucket inlines that name into date_trunc(..., col AT TIME ZONE '<name>') AT TIME ZONE '<name>'. The production database is built --with-system-tzdata on a distribution that ships the backward-compatibility zone names in a separate tzdata-legacy package, so the alias is unknown and the whole aggregation query fails.

The problem never reproduces locally: a Postgres image with bundled tzdata knows all 599 names.

What changed

SupportedTimeZones (new) reads pg_timezone_names once per JVM, lazily on the first chart request, and keeps the names the database accepts. For a zone the database does not know it substitutes the supported zone whose ZoneRules are identical, which is always the canonical target of the alias (Asia/Calcutta to Asia/Kolkata), and falls back to UTC when no equivalent exists. Results are memoised per requested zone.

GadgetAggregationsService resolves the zone at the top of aggregate, aggregateGrouped and aggregateItems, before the cache key is built and before the DAO is called, so no caller can reach the aggregation SQL with a name the database would reject. aggregateScalar (KPI) carries no zone and is unchanged.

ChartQuery.withZone derives a query with the resolved zone.

Behaviour

• Users on a zone the database already knows are unaffected: the same ZoneId is returned, so the generated SQL and cache keys are byte-identical to before.

• Users on an alias get correct data instead of an error, bucketed in a zone with identical rules.

• Equivalent zones now share aggregation cache entries rather than splitting them, since the cache key is derived from the resolved zone.

• If pg_timezone_names cannot be read, the requested zone is used unchanged, which is the current behaviour, and the read is retried on the next request rather than cached as a failure.

Infrastructure follow-up

Installing tzdata-legacy on the database host fixes every legacy name at once and is the root-cause fix. The application change is what keeps a database rebuild, restore or move to managed Postgres from silently reintroducing a tenant-wide chart outage. Both are worth doing.

Out of scope

• CSV exports, the standalone web dashboard and notification rendering. They accept a time zone but format in Java and never pass the name to Postgres.

• The per-path approval_config.timezone column, which is only ever read and written as a value.

• Frontend changes. The zone sent by the client is unchanged.

Acceptance criteria

• A chart request with tz=Asia/Calcutta returns data instead of 500, bucketed identically to Asia/Kolkata.

• Users on a zone the database supports see no change in data, SQL or cache behaviour.

• No chart endpoint can issue aggregation SQL with an unresolved zone name.

• Charts still work when pg_timezone_names is unreadable, degrading to today's behaviour.

Verification

• Resolution covers every zone Jira can send. Simulated against a database holding no alias names at all (340 of 591 zones, stricter than the real tzdata-legacy split): of 604 Java zone ids, 340 resolve to themselves and 258 substitute a zone with identical rules. The 6 that fall back to UTC are all SystemV/*, which Postgres does not know even with complete tzdata and which therefore already failed.

• Substitution does not move any bucket. Checked in Postgres across 14 alias-to-canonical pairs, all 5 granularities and hourly timestamps from 2018 to 2027: zero differing buckets, DST transitions in both hemispheres included. A deliberately wrong pair (Calcutta against UTC) differs on every row, confirming the check discriminates.

• Backend suite green: 1300 tests, 0 failures.