CosmosDB - DateTimeOffset Issue with UTC Querying

Question

There are issues with working with DateTimeOffset in CosmosDb. Referring to the guide of working with them on microsoft docs it states...

To convert a local DateTime to UTC, the offset must be known/stored as a property in the JSON and the client can use the offset to compute the UTC DateTime value.

So I'm storing my dates in my documents in the format of "2021-04-23T12:15:00+01:00". This works great when querying and displaying for users locally. However, I encounter issues when performing queries against these DateTimes...

On my server I will do a query like so...

Linq Query

.Where(x => x.ExpiresAt.UtcDateTime <= DateTime.UtcNow)

If the expiresAt = "2021-04-23T12:15:00+01:00"

And the UTC time now = "2021-04-23T11:15:00+00:00"

Then I would expect the query to return an item. However, no items are returned until an hour later, almost like .UtcDateTime extension does not work. If that is the case, then how do you compare DateTimeOffsets like this? The microsoft docs make it sound like you need another field just to store the offset and can't use it inline with the datetime itself. Like...

{
    "expiresAt":"2021-04-23T11:15:00",
    "expiresAtOffset":"1"
}  

Surely I'm misunderstanding it, right?

Note: I've tried using a SQL query instead of linq like so...

"SELECT * FROM c WHERE c.expiresAt <= \"2021-04-23T11:58:05\""

But still the same issue, because the problem is the cosmos value is the one that needs converting and viewing the docs there's no way of converting the cosmos value into a UTC time...

Answer 1

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.