MongoDB .NET Driver - Search on DateTime field using $dateAdd-CodePudding

I'm going to search DateTime values in MongoDB using MongoDB.Driver in C#. The DateTime objects are stored with two subfields: DateTime and offset. I wish to search on the shifted DateTimes, meaning the offset (in integer, showing seconds) to be added to the DateTime and then checked with my date search query.

The following code doesn't return an error, but also no result. It just keeps loading.

DateTime start = new(2022, 3, 18, 8, 24, 45);
DateTime end = new(2022, 3, 18, 8, 24, 50);


var addFields = BsonDocument.Parse("{\r\n $addFields:{\r\n \"dateWithOffset\" : {\r\n $dateToParts:\r\n {\r\n date:\r\n {\r\n $dateAdd:\r\n {\r\n startDate: \"$myfield.Timestamp.UTCtimestamp\",\r\n unit: \"second\",\r\n amount: \"$myfield.Timestamp.offset\"\r\n }\r\n }\r\n }\r\n }\r\n }\r\n }");

var match = new BsonDocument("dateWithOffset", new BsonDocument("$gte", start).Add("$lte", end));


var pipeline = _context.myCollection.Aggregate().AppendStage<BsonDocument>(addFields)
                .Match(match);


var list = pipeline.ToList();
List< myCollection> searchResult = list.Select(doc => BsonSerializer.Deserialize<myCollection>(doc)).ToList();
return searchResult;

To make the query clear, here I repeat it in a friendlier format:

{
    "dateWithOffset" : {
        $dateToParts : {
            date : {
                $dateAdd : {
                    startDate : "$myfield.Timestamp.UTCtimestamp",
                    unit : "second",
                    amount : "$myfield.Timestamp.offset"
                }
            }
        }
    }
}

I've read similar questions, but the problem persists.

CodePudding user response：

You don't need the $dateToParts operator.

Returns a document that contains the constituent parts of a given BSON Date value as individual properties. The properties returned are year, month, day, hour, minute, second and millisecond.

Your current query is comparing an object with DateTime values which will never be true.

Just need the $dateAdd operator.

{
  $addFields: {
    "dateWithOffset": {
      $dateAdd: {
        startDate: "$myfield.Timestamp.UTCtimestamp",
        unit: "second",
        amount: "$myfield.Timestamp.offset"
      }
    }
  }
}

Demo @ Mongo Playground

C# syntax

var addFields = BsonDocument.Parse(@"{
  $addFields: {
    ""dateWithOffset"": {
      $dateAdd: {
        startDate: ""$myfield.Timestamp.UTCtimestamp"",
        unit: ""second"",
        amount: ""$myfield.Timestamp.offset""
      }
    }
  }
}");