UTC String vs DateTimeOffset String in Cosmos DB: Trade‑off for Regional Time‑Zone Handling

Answer

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.

Confirmed facts

• Cosmos DB indexes string properties lexicographically; range filters (BETWEEN, >, <) work correctly only when the stored values are comparable in that order.
• An ISO‑8601 UTC string (e.g., "2026-09-30T12:00:00Z") always sorts chronologically because the offset is fixed at zero.
• A DateTimeOffset string (e.g., "2026-09-30T08:00:00-04:00") varies its offset per record, so lexical ordering does not match chronological order unless the offset is stripped or normalized.
• Storing a DateTimeOffset adds roughly 2‑6 extra characters per value (the offset) and slightly hinders string‑based compression because the offset values differ.
• Cosmos DB does not provide server‑side time‑zone conversion in the SQL API; conversion must be done in the application layer.

Likely explanation

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.

Recommendation

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.

Indexing strategy when DateTimeOffset is required

1. Store the DateTimeOffset value in a property such as "timestampOffset".
2. Add a second property, "timestampUtc", that holds the same instant converted to UTC and formatted as an ISO‑8601 string.
3. Index "timestampUtc" (the default path is sufficient) and use it in all range queries.
4. When reading, retrieve "timestampOffset" to show the original wall‑clock time or to apply the offset for display.

This pattern preserves the original offset while keeping the index‑driven query path efficient.

Missing diagnostic detail

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.