Can I use the DateTimePart function against a DateTimeOffset serialized object in Azure Cosmos?

Question

I am attempting to execute a query against my Cosmos database using the DateTimePart function:

SELECT c.callTime,c.id,c.source,DateTimePart('m', c.callTime) as month FROM c where c.ownerId='3467nm6df'

But in my results from that query, I get nothing in the 'month' field:

{
    "callTime": "2020-09-30T20:17:28+00:00",
    "id": "352hyKL5agNtB4ic",
    "source": "five9"
}

The documents in my cosmos database are structured like this:

public class CallIndex
{
    public string id;
    public DateTimeOffset callTime;
    public string source;
}

Some example data looks like this:

{
    "callTime": "2020-09-30T20:17:32+00:00",
    "id": "352WMvL5agNwiU7u",
    "source": "five9"
},
{
    "callTime": "2020-09-30T20:17:28+00:00",
    "id": "352hyKL5agNtB4ic",
    "source": "five9"
},
{
    "callTime": "2020-09-30T20:16:51+00:00",
    "id": "352iS3L5agN4yAog",
    "source": "five9"
}

In doing some research, I am finding conflicting information about support for the DateTimeOffset type in Azure Csosmos. In some articles, it is pointed out that Cosmos supports DateTimeOffset. In other articles, it is pointed out that Cosmos does not support DateTimeOffset and that I should use DateTime instead.

We have already gone far down the path of using DateTimeOffset in our code. And it has actually worked fine, until we attempted the above query. We store everything in UTC, and we use the DateTimeOffset in code to be able to easily convert to client time zones in reports. So my question is, is the format 2020-09-30T20:16:51+00:00 (ISO DateTime with offset) supported by the function DateTimePart? Or does DateTimePart only work against a format like this: 2020-09-30T20:16:51.000000Z (ISO DateTime format)?

If the latter is true, then we will need to convert all of the existing data to the ISO 8061 DateTime format instead of the ISO 8061 DateTime format with offset. If we end up doing that, how can I tell Newtonsoft how to render a DateTimeOffset object as ISO 8061 DateTime (without offset)?

Answer 1

Yes. The recommended format for DateTime strings in Azure Cosmos DB is yyyy-MM-ddTHH:mm:ss.fffffffZ which follows the ISO 8601 UTC standard. The format you show above will not work with the DateTime system functions in your queries.