Node.js/MongoDB - querying dates

Question

I'm having a bit of an issue understanding how to query dates; I think the issue might be with how my data is structured. Here is a sample document on my database.

{

    "phone_num": 12553,
    "facilities": [
        "flat-screen",
        "parking"
    ],
    "surroundings": [
        "ping-pong",
        "pool"
    ],
    "rooms": [
        {
            "room_name": "Standard Suite",
            "capacity": 2,
            "bed_num": 1,
            "price": 50,
            "floor": 1,
            "reservations": [
                {
                    "checkIn": {
                        "$date": "2019-01-10T23:23:50.000Z"
                    },
                    "checkOut": {
                        "$date": "2019-01-20T23:23:50.000Z"
                    }
                }
            ]
        }
    ]
}

I'm trying to query the dates to see check if a specific room is available at a certain date-range but no matter what I do I can't seem to get a proper result, either my query 404's or returns empty array.

I really tried everything, right now for simplicity I'm just trying to get the query to work with checkIn so I can figure out what I'm doing wrong. I tried 100 variants of the code below but I couldn't get it to work at all.

  .find({"rooms": { "reservations": {   "checkIn" : {"$gte": { "$date": "2019-01-09T00:00:00.000Z"}}}}})

Am I misunderstanding how the .find method works or is something wrong with how I'm storing my dates? (I keep seeing people mentioning ISODates but not too sure what that is or how to implement).

Thanks in advance.

Firoz Shams · Accepted Answer · 2019-01-06 06:41:22Z

3

I think the query you posted is not correct. For example, if you want to query for the rooms with the checkin times in a certain range then the query should be like this -

.find({"rooms.reservations.checkout":{$gte:new Date("2019-01-06T13:11:50+06:00"), $lt:new Date("2019-01-06T14:12:50+06:00")}})

Now you can do the same with the checkout time to get the proper filtering to find the rooms available within a date range.

A word of advice though, the way you've designed your collection is not sustainable in the long run. For example, the date query you're trying to run will give you the correct documents, but not the rooms inside each document that satisfy your date range. You'll have to do it yourself on the server side (assuming you're not using aggregation). This will block your server from handling other pending requests which is not desirable. I suggest you break the collection down and have rooms and reservations in separate collections for easier querying.

answered Jan 6, 2019 at 6:41

Firoz Shams

1628 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

xerav Over a year ago

Thanks that worked! Quick question,The 'reservations' holds the date range's where a room is booked, I'm trying to achieve a query where the user will input the check in date and check out date and it will return all the available rooms during that date range. How do you suggest I go about this? Should my query search for rooms that are BOOKED during that date range and then I remove them from the total list of rooms? If not how would a query that achieves the above look like? Thanks again man!

Firoz Shams Over a year ago

You should use aggregation in that case. Use "$unwind" on the rooms and reservations subdocuments and then use a simple "$match" to find the rooms that satisfy the conditions. Here is the documentation for aggregation - docs.mongodb.com/manual/aggregation

xerav Over a year ago

I know this might be a stupid question but I'm new to mongoDB and nodeJS, I cant seem to use the aggregate function on my Hotel Model 'var Hot = mongoose.model('Hotel');' All the examples I see use db.collection.aggregate; how do I go about this when I am using the Hot var to query? Sorry I'm a total newbie

Firoz Shams Over a year ago

In your case it should Hot.aggregation([]). The array inside should resemble the mongodb aggregation pipeline.

pavel · Accepted Answer · 2020-07-19 10:31:53Z

Recently I was working on date query. First of all we need to understand how we store date into the mongodb database. Say I have stored data using UTC time format like 2020-07-21T09:45:06.567Z.

and my json structure is

[
{
    "dateOut": "2020-07-21T09:45:06.567Z",
    "_id": "5f1416378210c50bddd093b9",
    "customer": {
        "isGold": true,
        "_id": "5f0c1e0d1688c60b95360565",
        "name": "pavel_1",
        "phone": 123456789
    },
    "movie": {
        "_id": "5f0e15412065a90fac22309a",
        "title": "hello world",
        "dailyRentalRate": 20
    }
}
]

and I want to perform a query so that I can get all data only for this( 2020-07-21) date. So how can we perform that?. Now we need to understand the basic.

 let result = await Rental.find({
    dateOut: {
        $lt:''+new Date('2020-07-22').toISOString(),
        $gt:''+new Date('2020-07-21').toISOString()
    }
})

We need to find 21 date's data so our query will be greater than 21 and less than 22 cause 2020-07-21T00:45:06.567Z , 2020-07-21T01:45:06.567Z .. ... .. this times are greater than 21 but less than 22.

koteswararao pv · Accepted Answer · 2019-01-07 11:48:09Z

0

var mydate1 = new Date();

var mydate1 = new Date().getTime();

ObjectId.getTimestamp()

Returns the timestamp portion of the ObjectId() as a Date.

Example

The following example calls the getTimestamp() method on an ObjectId():

ObjectId("507c7f79bcf86cd7994f6c0e").getTimestamp()

This will return the following output:

ISODate("2012-10-15T21:26:17Z")

answered Jan 7, 2019 at 11:48

koteswararao pv

892 silver badges9 bronze badges

Comments

sai krishna · Accepted Answer · 2021-07-13 12:15:02Z

0

If your using timestamps data to query. EG : "createdAt" : "2021-07-12T16:06:34.949Z"

const start  = req.params.id; //2021-07-12
const data = await Model.find({
            "createdAt": {
              '$gte': `${start}T00:00:00.000Z`,
              '$lt': `${start}T23:59:59.999Z`
            }
          });
console.log(data);

it will show the data of particular date .i.,e in this case. "2021-07-12"

answered Jul 13, 2021 at 12:15

sai krishna

1396 bronze badges

Collectives™ on Stack Overflow

Node.js/MongoDB - querying dates

4 Answers 4

4 Comments

Comments

var mydate1 = new Date().getTime();

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

var mydate1 = new Date().getTime();

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related