Query entries with dates between two dates

Working with Data Node.js Frameworks
,
1

Hi, so I am trying to query reservations that are between two dates.
So for example, if I have a reservations from
2023/02/17 to 2023/03/19 ,
2023/03/20 to 2023/04/22 and
2024/06/19 to 2024/05/19
and check against a reservation for 2023/03/18 to 2023/03/28 would return the first two reservations.

My current code is this, but it returns basically all results. I am not sure on how to check if the reservation is between the two dates, since there’s also basically no “and” operator from what I was able to find in the docs.

 const reservations = await Reservation.aggregate([
        {
            $project: {
                        startDate: { $gte: [ "$startDate", new Date(startDate) ]},
                        endDate: { $lte: [ "$endDate", new Date(endDate)]},
            }
        }
    ]).exec();

Trying this

const reservations = await Reservation.aggregate([
        {
            $project: {
                        startDate: { $gte: [ "$startDate", new Date(startDate) ], $lte: [ "$startDate", new Date(endDate) ]},
                        endDate: { $lte: [ "$endDate", new Date(endDate)], gte: [ "$endDate", new Date(startDate) ]},
            }
        }
    ]).exec();

results in an error “Invalid $project :: caused by :: FieldPath field names may not start with ‘$’. Consider using $getField or $setField.”

Anybody able to help?

2

This will also not work:

$match: {
                    startDate: {
                        $and: [
                            {
                                $gte: ["$startDate", new Date(startDate)]
                            },
                            {
                                $lte: ["$startDate", new Date(endDate)]
                            }
                        ]
                    },
                    endDate: {
                        $and: [
                            {
                                $lte: ["$endDate", new Date(endDate)]
                            },
                            {
                                $gte: ["$endDate", new Date(startDate)]
                            }
                        ]
                    },
            }
3

What about

start = new Date( startDate )
end = new Date( endDate )

$match :
{
   startDate : { $gte : start , $lte : end }
   endDate : { $lte : end , $gte : start }
}
4

That returns an empty array, so it seems like it doesnt find these.

Input:
startDate: 2025-04-18T13:30:00.000Z
endDate: 2026-04-20T14:30:00.000Z

All entries:


_id: 65cd5b32c1f7e198b373591d
startDate: 2025-04-19T08:24:36.000+00:00
endDate: 2026-02-07T00:31:40.000+00:00
howManyPeople: 9
owner: "something@something.com"
__v: 0

_id: 65cf510f4624ae128a3f99fe
startDate: 2022-04-19T08:24:36.000+00:00
endDate: 2023-04-19T08:24:36.000+00:00
howManyPeople: 4
owner: "something@something.de"
__v: 1

This is also the same for just using the endDate or the startDate. Using $project will throw an exception

Code is this

 const reservationss = await Reservation.aggregate([
        {
                $match :
                    {
                        startDate : { $gte : startDate , $lte : endDate },
                        endDate : { $lte : endDate , $gte : startDate }
                    }
        }
    ])
5

My logic seems to work. See Mongo playground.

I what I shared, I used the variables start and end initialized with new Date() of startDate and endDate respectively so that the matched is not polluted with the same expressions twice. And to make it clear that they are variables rather than field names. A newbie might be confused and thing that startDate the field name is the same as startDate your variables.

What I suspect is that in your version of $match, new Date() is not called so the dates in the input

do not have the same type as the stored fields.

6

Hi there, yeah I completely oversaw that. Converting the JSON object into a Date indeed fixes the problem. Thank you very much.

1 Like