UTC-only timestamps versus offset-aware strings for DST‑safe regional queries in CouchDB

Store UTC-normalized keys and convert boundaries in the application

Use UTC-only timestamp strings (ISO 8601 ending in Z) as the CouchDB view key. The application must convert user-specified local-time ranges to UTC before issuing the query. Preserving the original offset in a separate document field is safe for auditing or display, but it cannot serve as the primary sort key for range queries.

Why offset-aware strings break range queries

CouchDB orders view keys lexicographically. ISO 8601 strings that carry different UTC offsets—such as 2026-10-25T02:30:00+02:00 and 2026-10-25T01:30:00Z—do not sort chronologically because the offset portion changes the character sequence. During a DST fall-back transition, an ambiguous local hour like 02:30 can appear twice with different offsets, or the same instant can be written with a Z suffix or a numeric offset. A startkey/endkey range over such strings will miss documents or include the wrong ones.

In CouchDB 3.x, the default JavaScript view server parses strings with new Date(), which normalizes them to UTC internally, but the view index is built from the emitted key string, not from a normalized Date object. The view server also lacks an IANA time-zone database, so it cannot resolve names like Europe/Berlin or determine whether a local timestamp fell before or after a transition.

Recommended pattern

Emit a single UTC-normalized string as the view key:

function (doc) {
  if (doc.occurredAtUtc) {
    emit(doc.occurredAtUtc, null);
  }
}

Store the original local representation separately if needed:

{
  "occurredAtUtc": "2026-10-25T00:30:00Z",
  "occurredAtLocal": "2026-10-25T02:30:00+02:00",
  "timezone": "Europe/Berlin"
}

The application converts the user's local start and end times to UTC using a robust library (for example, one backed by the IANA database) and passes those UTC strings as startkey and endkey. A single range query then returns exactly the documents whose absolute instants fall inside the requested window.

Why not split queries or add a secondary sort key?

Adding a secondary sort key or collation function inside the view cannot recover the information that was lost when the offset was discarded, nor can it supply the missing IANA rules. Splitting the query into multiple ranges for each possible offset adds complexity and still requires the application to know the DST rules for the user's region. Converting the boundaries once, before the query, is simpler and keeps the view index small and fast.

Verification

1. Insert documents that span a DST transition, including the ambiguous repeated hour.
2. Query the view with UTC startkey and endkey values that correspond to the local boundaries.
3. Confirm that every returned document's occurredAtUtc falls within the absolute window and that no document with an offset-aware key was missed.

This pattern assumes CouchDB 3.x with the default JavaScript view server. If your deployment uses a custom view server with time-zone data, the calculus could change, but that is uncommon.