Question
UTC String vs DateTimeOffset String in Cosmos DB: Trade‑off for Regional Time‑Zone Handling
Cig Cedar
0 reputation · 11 Jan 2021, 01:41 UTC
139.8K views
Loading…
0 reputation · 11 Jan 2021, 01:41 UTC
Goal: select a DateTime storage pattern in Azure Cosmos DB that enables accurate regional time‑zone display while preserving fast, index‑driven time‑range queries.
Constraints: the container must support efficient BETWEEN filters on UTC timestamps, avoid ambiguous values when daylight‑saving rules change, and keep storage overhead low. Storing a plain ISO‑8601 UTC string gives optimal indexing but requires the application to apply the correct offset on read, risking inconsistent conversions if time‑zone data diverges. Persisting a DateTimeOffset string retains the original zone, eliminating extra conversion logic, yet increases document size and can hinder range indexes that ignore the offset unless a separate UTC field is added. The trade‑off also affects latency and cost, and there is no built‑in server‑side time‑zone conversion in the SQL API.
Which approach yields the best balance of storage efficiency and query performance for UTC‑based filters? How does each pattern affect the ability to display correct local times after a DST rule change? What indexing strategy mitigates the range‑query penalty when using DateTimeOffset strings?
34275 reputation · 11 Jan 2021, 13:27 UTC
For UTC‑based range filters, storing timestamps as plain ISO‑8601 UTC strings gives the best balance of storage efficiency and query performance. DateTimeOffset strings preserve the original offset but increase document size and can degrade range‑query efficiency unless a separate normalized UTC field is added for indexing.
Because UTC strings guarantee correct lexical ordering, the index can satisfy range scans without extra computation, keeping request units (RUs) low and latency minimal. DateTimeOffset strings require either a full scan or an additional indexed property to achieve the same performance, which adds storage overhead and query complexity. For display, the application can convert the UTC instant to any target time zone using up‑to‑date zone data; if the original offset is needed for auditing, DateTimeOffset retains that information directly.
Use a single UTC‑only string property for the timestamp that is used in BETWEEN filters. Keep a separate, optional property (e.g., "originalOffset") only when the exact original offset must be preserved for compliance or audit purposes.
This pattern preserves the original offset while keeping the index‑driven query path efficient.
If a significant portion of your workload requires the original offset for every read (e.g., legal timestamp audits), the extra storage and indexing overhead of DateTimeOffset may be justified. Please confirm the expected percentage of queries that need the original offset versus those that only need the instant; this will affect whether the dual‑property approach is warranted.
Use comments to ask for clarification. Post a solution as an answer.
34,275 reputation · 11 Jan 2021, 10:07 UTC
When both a UTC string and its offset are stored, a composite index on timestampUtc ASC, timestampOffset ASC allows the query engine to narrow scans by the leading UTC key while preserving the offset for downstream conversion. Range filters on timestampUtc alone remain lexicographically efficient, but adding timestampOffset as a second index key avoids full collection scans when queries also filter on offset sign or magnitude. This pattern incurs minimal storage overhead, typically 5-10 bytes per document for the offset field, and keeps request-unit costs lower than re-indexing or full-scan alternatives. Verify RU differences in a staging container before promoting to production, especially for workloads with high cardinality or frequent DST-transition queries.
cosmosdb