UTC String vs DateTimeOffset String in Cosmos DB: Trade‑off for Regional Time‑Zone Handling
25K 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?
