filtering timebased data in MongoDB 5.0, c# driver: getting wrong (zero) results

Question

I have a TimeSeries Collection introduced with MongoDB5.0, filled with ~100000 documents and want the filter to work, e.g. get the document count of a specific timerange inside the collection.

// Timestamp must be a BSON date (as the doc's say), which is BSONDateTime in c#
// so the time range boundaries for the filter is based on the BSon Default 1/1/1970
// from, to are TimeSpan objects

var dt1 = new DateTime(1970,1,1,0,0,0, DateTimeKind.Utc) + from;
var dt2= new DateTime(1970,1,1,0,0,0, DateTimeKind.Utc) + to;

var filter = Builders<MongoTimeSeriesDocument>.Filter.Gte(x => x.Timestamp, dt1);
filter &= Builders<MongoTimeSeriesDocument>.Filter.Lte(x => x.Timestamp, dt2);

// result: zero
var docCount = await collection.CountDocumentsAsync(filter)
                .ConfigureAwait(false);
// result: zero
var docCount2 = await collection.Find(filter)
                .CountDocumentsAsync()
                .ConfigureAwait(false);

I also registered a DateTimeSeriealizer:

BsonSerializer.RegisterSerializer(typeof(DateTime), new DateTimeSerializer(DateTimeKind.Utc));

The serializer has no effect.

this works:

// result: 70000
var docCount = await collection.CountDocumentsAsync(x => x.Timestamp < new DateTime(2021,11,7,0,0,0, DateTimeKind.Utc))
                .ConfigureAwait(false);

while this doesn't work (it should count all, since timestamps are > 1970):

// result: 0
var docCount2 = await collection.CountDocumentsAsync(x => x.Timestamp > new DateTime(1970,1,1,0,0,0, DateTimeKind.Utc))
                .ConfigureAwait(false);
// same with BsonDateTime: result: 0
var docCount2 = await collection.CountDocumentsAsync(x => x.Timestamp > new BsonDateTime(new DateTime(1970,1,1,0,0,0, DateTimeKind.Utc)))
                .ConfigureAwait(false);

What's the problem? (no results, wrong results, error?) - btw. this may help: https://stackoverflow.com/questions/43167245/filter-only-by-date-using-mongodb-c-sharp-driver — Christoph Lütjen, Nov 07 '21 at 16:28
zero results is the issue. I believe, that the linked issue is a working filtering on DateTime, prior to the [TimeSeries](https://www.mongodb.com/developer/how-to/new-time-series-collections/) in Mongo. I assume the collection does not provide filtering like the normal collections do. — deafjeff, Nov 07 '21 at 18:45
Does comparing x.Timestamp with a new `BsonDateTime()` instead of a `DateTime` give you better results? — ToddBFisher, Nov 07 '21 at 19:20

score 1 · Answer 1 · answered Nov 12 '21 at 09:06

In c#, I go the BSon way and pass a $match string, which has 2 expressions for $gte and $lte. This works fine for the TimeSeries:

var filterStage = new StringBuilder();
            filterStage.Append("{$match:{$and:[ {$expr: {$gte:");
            filterStage.Append("[ \"$Timestamp\",");
            filterStage.Append($"ISODate('{isoFrom}'),");
            filterStage.Append("]}}, {$expr:{ $lte:");
            filterStage.Append("[ \"$Timestamp\",");
            filterStage.Append($"ISODate('{isoTo}')");
            filterStage.Append("]}} ]}}");

            var aggregation = await collection.Aggregate()
                .AppendStage<MongoTimeSeriesDocument>(filterStage.ToString())
                .ToListAsync()
                .ConfigureAwait(false);

filtering timebased data in MongoDB 5.0, c# driver: getting wrong (zero) results

1 Answers1