I am trying to retrieve records that have a “start_time” and “end_time” field in a mongo collection.

I am passing the start_time of the range of records I want from flutter as well as the end time of the range (currently the first and last of each month).

I have been able to get it to return records, but it is returning all records, even ones not in the range.

My GO code is currently below, which is returning records but that are out of the range:

    // Define the time format layout
    layout := "2006-01-02T15:04:05.999999"
    startTime, err := time.Parse(layout, scheduleRequest.StartDate)
    endTime, err := time.Parse(layout, scheduleRequest.EndDate)

    mongoStartTime := primitive.NewDateTimeFromTime(startTime)
    mongoEndTime := primitive.NewDateTimeFromTime(endTime)

    filter := bson.M{
        "_id": scheduleRequest.EmployeeID,
        "schedules.end_time": bson.M{
            "$gte": mongoStartTime, "$lte": mongoEndTime,
        },
    }

    ctx := context.TODO()
    scheudules, err := employeeCollection.Find(ctx, filter)
    if err != nil {
        log.Fatal(err)
    }

My records in the mongodb are as follows:

  {
    _id: ObjectId('66532bb6d8f4d2bba4da76bf'),
    first_name: 'John',
    last_name: 'Doe',
    date_of_birth: ISODate('0001-01-01T00:00:00.000Z'),
    department: '',
    contact_info: {
      email: '[email protected]',
      phone: '123-456-7890',
      address: {
        street1: '123 Main St',
        ity: 'Anytown',
        state: 'NY',
        zipcode: '12345'
      }
    },
    schedules: [
      {
        _id: ObjectId('665366a398e778c98cc2f92b'),
        date: ISODate('2024-05-26T16:43:15.931Z'),
        start_time: ISODate('2024-05-26T16:43:15.931Z'),
        end_time: ISODate('2024-05-27T00:43:15.931Z')
      },
      {
        _id: ObjectId('665366a398e778c98cc2f92c'),
        date: ISODate('2024-05-27T16:43:15.931Z'),
        start_time: ISODate('2024-05-27T20:43:15.931Z'),
        end_time: ISODate('2024-05-28T04:43:15.931Z')
      },
      {
        _id: ObjectId('665366a398e778c98cc2f92d'),
        date: ISODate('2024-05-29T16:43:15.931Z'),
        start_time: ISODate('2024-05-29T20:43:15.931Z'),
        end_time: ISODate('2024-05-30T04:43:15.931Z')
      },
      {
        _id: ObjectId('665366a398e778c98cc2f92e'),
        date: ISODate('2024-05-26T16:43:15.931Z'),
        start_time: ISODate('2024-06-26T16:43:15.931Z'),
        end_time: ISODate('2024-06-27T00:43:15.931Z')
      },
      {
        _id: ObjectId('665366a398e778c98cc2f92f'),
        date: ISODate('2024-05-27T16:43:15.931Z'),
        start_time: ISODate('2024-06-29T16:43:15.931Z'),
        end_time: ISODate('2024-06-30T00:43:15.931Z')
      },
      {
        _id: ObjectId('665366a398e778c98cc2f930'),
        date: ISODate('2024-05-29T16:43:15.931Z'),
        start_time: ISODate('2024-07-29T16:43:15.931Z'),
        end_time: ISODate('2024-07-30T00:43:15.931Z')
      }
    ]
  },

Here is out put I have from my program:

2024/05/26 12:56:16 Sent scheduleRequest range 2024-05-01T00:00:00.000 - 2024-05-31T00:00:00.000
2024/05/26 12:56:16 Parsed scheduleRequest range 2024-05-01 00:00:00 +0000 UTC - 2024-05-31 00:00:00 +0000 UTC
2024/05/26 12:56:16 Getting employee collection
2024/05/26 12:56:16 Filter: map[_id:ObjectID("66532bb6d8f4d2bba4da76bf") schedules.end_time:map[$gte:1714521600000 $lte:1717113600000]]

Here is what my program is printing as the records returned:

2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4a") Date:1716742569942 StartTime:1716742569942 EndTime:1716771369942}
2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4b") Date:1716828969942 StartTime:1716843369942 EndTime:1716872169942}
2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4c") Date:1717001769942 StartTime:1717016169942 EndTime:1717044969942}
2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4d") Date:1716742569942 StartTime:1719420969942 EndTime:1719449769942}
2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4e") Date:1716828969942 StartTime:1719680169942 EndTime:1719708969942}
2024/05/26 12:56:16 Employee Schedule: {ID:ObjectID("665369a9e4798ad33b805e4f") Date:1717001769942 StartTime:1722272169942 EndTime:1722300969942}

I have tried eveything I can think of, the weird thing is, when I pass a date range where NO records are in it does not return anything as expected:

Example of August requested returning no records as there are non:

2024/05/26 13:00:19 Sent scheduleRequest range 2024-08-01T00:00:00.000 - 2024-08-31T00:00:00.000
2024/05/26 13:00:19 Parsed scheduleRequest range 2024-08-01 00:00:00 +0000 UTC - 2024-08-31 00:00:00 +0000 UTC
2024/05/26 13:00:19 Getting employee collection
2024/05/26 13:00:19 Filter: map[_id:ObjectID("66532bb6d8f4d2bba4da76bf") schedules.end_time:map[$gte:1722470400000 $lte:1725062400000]]
2024/05/26 13:00:19 No employee schedule found

So in conclusion, if any record matches the date range, it returns ALL records even ones outside the range, and if no records match the date range. it returns NO records as expected.

I have tried the following queries:

    //Define the query to find schedules within the date range
    // filter := bson.M{
    //  "_id": scheduleRequest.EmployeeID, // Add this line to filter by employee ID
    //  "schedules": bson.M{
    //      "$elemMatch": bson.M{
    //          "$and": []bson.M{
    //              {"start_time": bson.M{"$gte": startTime}},
    //              {"start_time": bson.M{"$lte": endTime}},
    //          },
    //      },
    //  },
    // }

    filter := bson.M{
        "_id": scheduleRequest.EmployeeID,
        "schedules.end_time": bson.M{
            "$gte": mongoStartTime, "$lte": mongoEndTime,
        },
    }

    // filter := bson.M{
    //  "_id": scheduleRequest.EmployeeID,
    //  "$and": []bson.M{
    //      {"start_time": bson.M{"$gte": mongoStartTime, "$lte": mongoEndTime}},
    //      //{"end_time": bson.M{"$gte": mongoStartTime, "$lte": mongoEndTime}},
    //  },
    // }