DEV Community

Cover image for How to query documents in MongoDB that fall within a specified date range using Mongoose and Node.
Audiophile
Audiophile

Posted on

How to query documents in MongoDB that fall within a specified date range using Mongoose and Node.

Please Note: This tutorial assumes you have a working knowledge of javascript, the node run-time environment, mongoose queries and mongoDB

The Challenge:

So you work for a sales company called Super Doors as a developer and your boss, a Senior dev has been tasked with finding all customers that were unfortunate enough to purchase some bulletproof doors from the 24th - 30th of the month. Turns out the doors weren't so super after all and management needs a list of all the purchases made within those days for proper damage control. Lucky for you, your boss can't be bothered with such tasks and he assigns it to you. Next he goes, 'you know what? Why not just make it a feature on the platform for users to be able to query the database for customers within any range of dates?'

On the frontend:

You're meant to build a date range component that sends the startDate and the endDate to the backend. I won't go through building this as our concern is how to handle using dates on the backend to query the database. The point here is to define what values we're expecting as request parameters for the query.

Approach:

It's always a good idea to actually write out in words how you intend to approach the problem. You can be as specific or vague about it as you want, as long as it keeps you on track. Here's mine:

  1. check that date is not empty
  2. check that date is in proper format for query
  3. Query database using Mongoose
  4. Handle response.

On the backend.

We would need a Transaction model to hold information about each transaction made and a reference to an arbitrary User model. We would create a folder called 'models' and within it create a js file called 'transaction.js'.

Transaction Model:



const mongoose = require('mongoose');


const transactionSchema = mongoose.Schema({
     _id:{ type: mongoose.Schema.Types.ObjectId, ref: 'User'},
     name: String,
     email: {
              type: String,
              unique: true,
              match:`Regex match to ensure that string follows email format`  
             },
    address: String,
    phone: String,
    door:{ 
          name:String, 
          price: String
         },
   payment_status: {
            type: String,
            enum: ['Not Paid', 'Partial Payment', 'Paid'],
            default: 'Not Paid'
                   },
  date_paid: Date,

  date_delivered: Date          
});

module.exports = mongoose.model('Transaction', transactionSchema); 



Enter fullscreen mode Exit fullscreen mode

Controller Function:

Our controller function is where all the magic happens. Just like we did for the transaction.js file above, we would create a folder called 'controllers' and within it create a file called 'transaction.js'.



const Transaction = require('../models/transaction');


exports.getTransactionByDate = async(req, res) => {

try {
   //get dates from req.query by es6 object destructuring

    let { startDate, endDate } = req.query;

  //1. check that date is not empty
   if(startDate === '' || endDate === '') {
   return res.status(400).json({
       status:'failure',
       message: 'Please ensure you pick two dates'
        })
       }

  //2. check that date is in the right format
 //expected result: YYY-MMM-DDD
   console.log({ startDate, endDate});


//In some cases you'll get a date-time format where you have to separate the date
//from the time.


//3. Query database using Mongoose
//Mind the curly braces
const transactions = Transaction.find({ 
  date_paid: {
        $gte: new Date(new Date(startDate).setHours(00, 00, 00))
        $lt: new Date(new Date(endDate).setHours(23, 59, 59))
         }
  }).sort({ date_paid: 'asc'})  
}

//4. Handle responses
if(!transactions) {
return res.status(404).json({
 status:'failure',
 message:'Could not retrieve transactions'
})
}


res.status(200).json({
status:'success',
data: transactions
   })

} catch(error) {
  return res.status(500).json({
     status:'failure',
     error: error.message
        })
 }

}


Enter fullscreen mode Exit fullscreen mode

Woah, okay, slow down...what just happened?

  • We got the required parameters from our request query object:


    let { startDate, endDate } = req.query;


Enter fullscreen mode Exit fullscreen mode
  • We checked that neither parameters were empty and handled the response should that be the case.


    if(startDate === '' || endDate === '') {
   return res.status(400).json({
       status:'failure',
       message: 'Please ensure you pick two dates'
        })
       }

  //2. check that date is in the right format
 //expected result: YYY-MMM-DDD
   console.log({ startDate, endDate});


Enter fullscreen mode Exit fullscreen mode

It is easier to get the date in Year-Month-Date format since that is the default way javascript handles dates

  • We queried the database using the mongoose find and sort method. We also made use of mathematical operations, namely 'greater than or equal to'--$gte and 'less than'--$lt. The sort method was just a little extra I added to sort our list of transactions in ascending order, it's not inherently necessary.


   const transactions = Transaction.find({ 
  date_paid: {
        $gte: new Date(new Date(startDate).setHours(00, 00, 00))
        $lt: new Date(new Date(endDate).setHours(23, 59, 59))
         }
  }).sort({ date_paid: 'asc'})  
}


Enter fullscreen mode Exit fullscreen mode

Now you may be confused by what this new Date(new Date(startDate).setHours(00, 00, 00)) and this new Date(new Date(endDate).setHours(23, 59, 59)) does...Don't worry, got you covered.

When you call new Date() and pass it a date in string format, it returns the date you passed to it in date-time format. Then when we call new Date('date').setHours(); we get the chance to specify the time of day we want to start searching from in hour-minute-second format. In the case above, we want to start searching from the very beginning of our start date, so we have new Date('date').setHours(00,00,00);. Here's a short snippet from my PC to explain it further:

Alt Text

In the same manner, we want to get transactions up to but not beyond the last minute of our endDate, so we type new Date('date').setHours(23,59,59);.

But that's not all is it? We still have to wrap our new Date().setHours() in another new Date(). Why? Because as you can see from the snippet above, new Date().setHours() gives us what we want in seconds!(or is it milliseconds? I'm not sure). The point is, that's not the format we want, it's not legible enough.

So we wrap up new Date().setHours() to become new Date(new Date().setHours()) and we get our date in date-time format to the specific time we want!

  • We handle responses. Both for success and failure. In three statements: -If we don't get our transactions, notify the client. -If we get our transactions however, return the data to the client side. -All other exceptions, catch!


  if(!transactions) {
return res.status(404).json({
 status:'failure',
 message:'Could not retrieve transactions'
})
}


res.status(200).json({
status:'success',
data: transactions
   })

} catch(error) {
  return res.status(500).json({
     status:'failure',
     error: error.message
        })
 }


Enter fullscreen mode Exit fullscreen mode

If you want to search for records within a single day, it's pretty much the same concept, only this time you have the same startDate and endDate, the difference would be in the time part of the query

Our Router:

Here we listen for get requests on the specified path of our choice and tell our getTransactionByDate function to handle the event for us.



  const express = require('express');
  const { getTransactionByDate } = require('../controllers/transaction');

const API = process.env.BASE_URL;
const router = express.Router();

router.get(`${API}/date_range`, getTransactionByDate);

module.exports = router;



Enter fullscreen mode Exit fullscreen mode

I hope this has been helpful. If it has, like and comment! Also share to anyone you think may be having a hard time with querying NoSQL databases within a specific date range.

Top comments (7)

Collapse
 
asharlohmar profile image
Lohmar ASHAR • Edited

Actually that query is not exactly correct... in this form you will be missing 1 second worth of data... all the records between the 23:59:59 of the endDate and the 00:00:00 of the next day

If you want you're search to be "endDate day inclusive" than you should add a day to the endDate and use $lt with the 00:00:00 of that date, otherwise you should stick with a "endDate day exclusive" search and use the 00:00:00 of the endDate for the $lt part of the query (just make sure that user knows that).

Another point you should've made present (hope this is English enough) would be that this is the only correct way to do a search for a value in an interval, be it for Date or other datatype, and that a query like:

{ 
 date_paid: { $gte: <some date>}, 
 date_paid: { $lt: <some other date>}
}
Enter fullscreen mode Exit fullscreen mode

would produce other (unexpected and undesired) results.
Later edit: actually now I have to make sure if this is true, but I'm very confident that it is.
Later-later edit: actually I was right. You can see some sample tests here (for the query in the right way) and here (for the query in the "wrong form"/that produces the undesired results).
Side note: actually, if you think about it, making the search "endDate day inclusive" is pretty counter-intuitive from a user-experience point of view, if you'd want to get a day's worth of data you'd have to set both the startDate and endDate to the same value ... just my 2 cents.

Collapse
 
mrshadowgames profile image
MrShadowGames

Call me picky, but I prefer sticking with the same syntax that I use in direct queries to a MongoDB:

Instead of

date_paid: {
        $gte: new Date(new Date(startDate).setHours(00, 00, 00))
        $lt: new Date(new Date(endDate).setHours(23, 59, 59))
         }

Enter fullscreen mode Exit fullscreen mode

(Also, I wonder if that actually works, since there is a comma missing)

I'd use:

$and: [{ date_paid: { $gte: ... } }, { date_paid: { $lt: ... } }]
Enter fullscreen mode Exit fullscreen mode

But again, it's me probably just being notoriuosly picky

Collapse
 
monfernape profile image
Usman Khalil

I was recently facing the very same problem. You made it quite clear

Collapse
 
itz_giddy profile image
Audiophile

I'm glad it helped!

Collapse
 
rabbyhossain profile image
Rabby Hossian

Great article

Collapse
 
divee789 profile image
Divine Olokor

Please can someone provide the client side implementation please

Collapse
 
desmond_odion_27656c9a736 profile image
Desmond Odion

The code was exquisite but you forgot to await. Thanks