Azure-docs: Please draw attention to need to discard the offset information

[lukepuplett]

The page is confusing because it makes it sound like Cosmos is well-designed by storing as ISO 8601. However, its serializing a DateTime to the the UTC version of the ISO format, which carries no offset information.

Therefore, as I understand it, an object or document with a DateTimeOffset or ISO 8601 format time string that is local to the observer, such as a JSON birth certificate, containing an offset will lose this information when persisted.

This can be a major legal issue for software. Discarding the offset from UTC at the time of the event is destructive. In 20 years time, trying to work out when an event occurred in the local time means keeping a record of the timezone rules in place in that geography 20 years ago.

If the daylight saving rules change, for example Brazil scrapped DST this year, then going back and working out if someone was late for work, a contract signed or even when a person died, will be wrong unless it keeps track of rules changes.

As you can see, this can be a very important issue in software development and needs clarifying (I have misunderstood this page) and calling out. Thank you.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: c256dbf4-122d-1569-c4c3-0e401766c712
• Version Independent ID: 747c3f05-167d-2048-6f1b-fa1d7d856f66
• Content: Working with dates in Azure Cosmos DB
• Content Source: articles/cosmos-db/working-with-dates.md
• Service: cosmos-db
• GitHub Login: @SnehaGunda
• Microsoft Alias: sngun

[NavtejSaini-MSFT]

@lukepuplett Thanks for the question and we are assigning this case to doc author for further clarification. Meanwhile you can see the following similar issue raised for this doc and answered by our author if it helps.
https://github.com/MicrosoftDocs/azure-docs/issues/16802

@SnehaGunda Please check user question and respond or update as appropriate.

[lukepuplett]

Thanks guys. If you search that page in your browser for DateTimeOffset it is not mentioned at all.

Considering this statement:

These uses for DateTimeOffset values are much more common than those for DateTime values. As a result, DateTimeOffset should be considered the default date and time type for application development.

Source: https://docs.microsoft.com/en-us/dotnet/standard/datetime/choosing-between-datetime?view=netframework-4.8

Then it seems that the information on this page for Cosmos DB is either implying that it does not support DateTimeOffset or its out-of-date.

For example, the Order C# model uses DateTime but many people today will have chosen DateTimeOffset for the OrderDate, and called it OrderTime to reflect that its time component is interesting and represents an instant in time when an event occurred.

The ShipDate is different since this is presumably a day in the future on which the order should be shipped and so it might be UTC as its an instant in the future that needs to be synchronised or trigger a server-side action etc.

A ShippedTime would then be DateTimeOffset and capture the timezone information of the warehouse location.

In any case, I think the page would be more useful and aligned with Microsoft's other guidance if it dealt with an Order model that has at least one DateTimeOffset.

Thank you.

[lukepuplett]

@NavtejSaini-MSFT Yes, I think #16802 is a duplicate. It's closed but the page is still lacking. We should close this issue and reopen the other?

[NavtejSaini-MSFT]

@lukepuplett It would be better if we keep this issue open as the other has been closed long back. Any issue or enhancements required will be done by author here. So please keep this issue open and comment on this issue alone.

[jfcomst]

As the person who opened the other related issue a while back, I since found a Microsoft page that was useful for understanding how to work with date time offsets. It is not specific to cosmos db, but I found it useful for working with datetimeoffsets when I was working with a cosmos db. It's the document describing how to do date time round tripping, and it contains a section that deals with datetimeoffset as well. I think you should consider at least linking to it.

https://docs.microsoft.com/en-us/dotnet/standard/base-types/how-to-round-trip-date-and-time-values

This document really got me started on getting this working. However, I found that when asp.net deserialized the text date it always seemed to put it into the local time of the server, so it may still be necessary to store the original time and offset separately to support the scenario outlined by Luke.

[NavtejSaini-MSFT]

@lukepuplett Please let us know if the above link has helped you or you need further help in this regard.

[lukepuplett]

It's not help that I need; it's that the documentation is lacking.

As per @jfcomst original issue to "include details about using DateTimeOffset", the article still lacks details about using DateTimeOffset.

Consider that the article is titled Working with dates in Azure Cosmos DB and yet it doesn't mention the recommended BCL type for dates and times, i.e. DateTimeOffset even once. That's not right.

That page discusses storing DateTime and gives an example POCO with DateTime properties. It leaves the reader clueless about how it handles DateTimeOffset.

Reading this document I get the idea that Cosmos DB itself only sees strings, and that range queries are lexical on those ISO formatted strings.

There's some problems here that need addressing in the article.

• Does the SDK serialize DateTimeOffset as ISO 8601?
• If so, does it convert them to UTC?
• If so, then it will discard the offset information - that's destructive (it will _not_ round-trip) and needs a warning and some tips for ways to preserve this information.
• If not, then how would a lexical range query work if the offsets are not normalised?

Does that make sense?

Thanks.

[SnehaGunda]

@lukepuplett @jfcomst Apologies for the delay in responding to this issue. Actually my response in the other PR isn't right, though that's what I learned from our team initially (Sorry about the confusion). Upon further discussion here is what I learnt, Thanks to @ginamr for sharing this information:

"Azure Cosmos DB is scoped to the JSON types which does not include a DateTime type. So, in Azure Cosmos DB, dates must be stored as strings. Currently, Azure Cosmos DB doesn't support localization of dates. The recommended format for DateTime strings in Azure Cosmos DB is YYYY-MM-DDThh:mm:ss.sssZ which follows the ISO 8601 UTC standard. Formatting the strings in this format will allow sorting dates lexicographically. Logic to handle non-UTC dates must be defined by the client."

I submitted a PR to clarify this in the doc. Hope this helps.

[lukepuplett]

Thank you for investigating. Yes, this sounds like super important information, so I'm happy to hear that we've both learned something :) and this story definitely needs telling in the documentation.

Applications developers need to store the offset in another property and then reconstruct a DateTimeOffset from the string date-time Cosmos document property, and the numeric offset property.

Thinking out aloud - I'm wondering whether it would be best if my own POCO models use only the same JavaScript types as Cosmos supports, which would make me and other developers stop and consider the limitations of Cosmos and make more deliberate choices.

Thanks.

[SnehaGunda]

@lukepuplett Rather than saying that your applications should only use the same JavaScript types as Cosmos supports, it best to say the following:

Cosmos DB supports JSON types: string, number, boolean, null, array, object. Dates can be represented either as strings or a number (epoch/timestamp) types. It is recommended to store all dates as UTC. If non-UTC dates are stored, the logic must be handled at the client-side. To convert a local DateTime--to--> UTC, the offset must be known/stored as a property in the JSON -- but the client would have to use the offset to compute UTC which could be error-prone if the client doesn't define the offset correctly. So as long as the client defines the offset correctly, there is no need to scope to just the type supported by Cosmos DB.

I made some more updates to the doc wrt to the above clarification. There are some improvements in our backlog for the DateTime functionality. We will let you know when they are available. If you have any feature request please feel free to log a request in our user voice, it will help us prioritize.

We will close this issue if there aren't further questions.

[SnehaGunda]

please-close