Azure CosmosDB : problem with datetime (timestamp)

Question

I follow this page to find a better way to query based on a timestamp

I use the Cosmonaut library and this is the cosmos DB settings

var cosmosSettings = new CosmosStoreSettings(cosmosDbName, endpointUrl, key, settings: setting =>
        {
            setting.IndexingPolicy = new IndexingPolicy(
                new RangeIndex(DataType.String, precision: -1), 
                new RangeIndex(DataType.Number, precision: -1));
        });

and then I try to query base on the datetime as following

    public async Task<IEnumerable<collectionNameObject>> GetAsync(GetCollection query)
    {
        var result = await _objectStore
            .Query(new FeedOptions {PartitionKey = new PartitionKey(query.x)})
            .Where(r =>
                r.y == query.y
                && r.z == query.z
                && r.Timestamp.Date >= query.Date.Date)
            .ToListAsync();
        return result;
    }

and here is what I have saved in CosmosDb

 [CosmosCollection("collectionNameObject")]
public class collectionNameObject: Entity
{
    [CosmosPartitionKey]
    [JsonProperty("x")]
    public string x{ get; set; }

    [JsonProperty("z")] public string z{ get; set; }

    [JsonProperty("y")] public string y{ get; set; }

    [JsonProperty("timestamp")] public DateTime Timestamp { get; set; }
}

The problem is that the query result is always empty, however, if I remove the timestamp filter, I get what I expected. It is not clear what I miss, so I wonder if anyone has a better suggestion or a hint?

Sajeetharan Sajeetharan · Accepted Answer · 2019-11-11T13:08:34

I guess since you are storing it as a string, it does not get reflected when you query it as a DateTime. The easiest way to do this is to implement a custom serializer & deserializer for dealing with JSON.

[JsonConverter(typeof(EpochDateTimeConverter))]
public DateTime Timestamp { get; set; }

I would recommend you to go through Working with Dates in Azure Cosmos DB and Example

Azure CosmosDB : problem with datetime (timestamp)

1 Answers