Couchbase N1QL DATE_FORMAT_STR offset handling for mixed regional ISO 8601 timestamps

Direct answer

DATE_FORMAT_STR can convert mixed-offset ISO 8601 strings at query time, but it cannot make range scans on the original string field chronologically correct across offsets. For index-friendly ordering across regions, write-time normalization to a UTC epoch number (BIGINT) or a UTC ISO 8601 string is the practical way to preserve GSI range-scan performance. DATE_FORMAT_STR is acceptable for formatting a small, already-filtered result set; it is not a substitute for an indexed UTC column when you filter by time over large datasets.

Why mixed-offset strings break ordering

Couchbase GSI string indexes sort lexically. 2026-10-16T10:00:00+05:30 sorts before 2026-10-16T05:00:00+00:00 as text, even though the first instant occurs later. Because the offsets differ, lexical order does not equal instant order, so a range predicate on the raw string field returns the wrong time window.

What DATE_FORMAT_STR does and does not do

DATE_FORMAT_STR parses a valid ISO 8601 string and renders it in a target time zone at query time. It does not create an indexed expression on the converted value, and Couchbase does not publish explicit performance thresholds for millions of documents. Expect full-scan behavior when you apply it to every document in a large bucket, and treat any specific throughput number as unverified until you test it.

Recommended steps for this case

1. Add a write-time field such as event_utc_epoch (BIGINT milliseconds since epoch) or event_utc_iso (ISO 8601 in Zulu).
2. Create a GSI on that field: CREATE INDEX idx_event_utc_epoch ON bucket(event_utc_epoch);
3. Run reporting range scans against the UTC field, then apply DATE_FORMAT_STR only to the returned rows for display.
4. Keep the original string for audit, but do not rely on it for chronological range queries.

Verification

On a non-production bucket, insert sample documents at mixed offsets, create a GSI on the raw string, and run EXPLAIN on a range query. Compare the index usage and row order against a query on a UTC epoch field with the same time window.

Missing diagnostic detail: do your reporting queries filter by timestamp range over the full dataset, or only format timestamps after another selective predicate has already reduced the result set? That answer determines whether write-time normalization is required or whether query-time formatting is acceptable.