CosmosDB - DateTimeOffset Issue with UTC Querying
Another Question On StackOverflow suggested storing it with the Z abbreviation.I tried that and it seems to be working roughly how I want it to, but not perfect. All dates are now stored as DateTime values with Z appended. Then using DateTimeOffset DTO's it will convert the value appropriately on the client.Had to change the serialization settings for it though, like so...
var jsonSerializerSettings = new JsonSerializerSettings { DateFormatHandling = DateFormatHandling.IsoDateFormat, DateFormatString = "yyyy-MM-ddTHH:mm:ss.fffZ" };
Then I added that when creating the DocumentClient...
new DocumentClient(serviceEndpoint, authKey, serializerSettings: jsonSerializerSettings);
The only issue I have with this is that I have emails that need to be sent from the server with times in them. These times are incorrect because the server does not know how to convert them to the clients local time.
In my experience, this combination works to achieve happiness with Cosmos and datetimes on .NET:
- Model dates and times as ISO 8601 values as suggested in the docs
- Use
DateTime
.NET type, notDateTimeOffset
- Ensure full datetimes are serialized in exact format
yyyy-MM-ddTHH:mm:ss.fffffffZ
- Always store UTC values, never local times
- If value represents something occurring in a specific time zone, store the IANA time zone alongside the value. Then use this for converting before display to users.
- Use Stream-based methods (sample) in .NET SDK 3 and
System.Text.Json
for direct control over serialization, and for a default that works as expected - Avoid LINQ, instead construct queries from strings using QueryDefinition