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); 

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
        })
 }

}

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

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

    let { startDate, endDate } = req.query;

• 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});

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'})  
}

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:

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
        })
 }

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;

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.