MongoDB: Select documents in a period of time (last day, week, month, year)

Introduction

When working with MongoDB, querying documents based on time is a common requirement, especially for applications featuring time series data or log entries. Understanding how to effectively retrieve documents within a certain time period can unveil trends or insights about your data. In this tutorial, we’ll go through a step-by-step approach on how to select documents from the last day, week, month, and year using MongoDB queries.

Prerequisites

• Basic understanding of MongoDB and its query syntax.
• An installed version of MongoDB (version 4.0 or later).
• A dataset with documents that include a date field to filter by.

Selecting Documents from the Last Day

To select documents from the last day, we’ll calculate the start time of the previous day and query documents with a date field greater than this computed time.

const dayAgo = new Date();
dayAgo.setDate(dayAgo.getDate() - 1);

// MongoDB query
const query = {
  createdAt: {
    $gte: dayAgo
  }
};

const documentsFromLastDay = db.collection.find(query);

This query will return all documents created within the last 24 hours.

Selecting Documents from the Last Week

Retrieving documents from the last week involves a similar approach by calculating the start time of the same day of the previous week.

const weekAgo = new Date();
weekAgo.setDate(weekAgo.getDate() - 7);

// MongoDB query
const query = {
  createdAt: {
    $gte: weekAgo
  }
};

const documentsFromLastWeek = db.collection.find(query);

Documents with a createdAt date within the last 7 days will be returned.

Selecting Documents from the Last Month

For selecting documents from the last month, we require a way to subtract one month from the current date. We can achieve this by manipulating the month directly.

const monthAgo = new Date();
monthAgo.setMonth(monthAgo.getMonth() - 1);

// MongoDB query
const query = {
  createdAt: {
    $gte: monthAgo
  }
};

const documentsFromLastMonth = db.collection.find(query);

Any document that has been created in the last 30 to 31 days, depending on the month, will be included in the result set.

Selecting Documents from the Last Year

Selecting documents from the last year requires subtracting a year from the current data and querying documents in a similar manner.

const yearAgo = new Date();
yearAgo.setFullYear(yearAgo.getFullYear() - 1);

// MongoDB query
const query = {
  createdAt: {
    $gte: yearAgo
  }
};

const documentsFromLastYear = db.collection.find(query);

This query returns all documents created in the past 365 days or 366 for leap years.

Advanced Time-Based Aggregation

We can use MongoDB’s aggregation framework for more complex time-based queries. For instance, counting documents or getting averages of certain values within specified timeframes.

// Count documents from the last month
const documentsCount = db.collection.aggregate([
  {
    $match: {
      createdAt: {
        $gte: monthAgo
      }
    }
  },
  {
    $group: {
      _id: null,
      count: { $sum: 1 }
    }
  }
]);

// Find the average of a 'value' field of documents from last month
const averageValue = db.collection.aggregate([
  {
    $match: {
      createdAt: {
        $gte: monthAgo
      }
    }
  },
  {
    $group: {
      _id: null,
      average: { $avg: "$value" }
    }
  }
]);

These examples aggregate documents based on their creation date, providing you with insights on document counts and averages for a certain period.

Optimization Tips

When you are working with larger datasets, it is important to optimize queries for performance.

• Use Indexes: Ensuring there is an index on the date field can significantly speed up the query operation.
• Projection: Limit the fields returned by the query if you do not need the entire document. This reduces the amount of data MongoDB has to read and transfer over the network.
• Avoid Using $ne or $not on Indexed Fields: These operators can negate the benefits of indexing and lead to full collection scans.

Dealing with Time Zones

Date and time storage in MongoDB are in UTC by default. If your application operates in different time zones, consider converting times to UTC before querying or use the $dateFromParts operator to deal with time zone conversions within your queries.

Conclusion

In this tutorial, we’ve covered how to query documents from MongoDB over varying time periods. Employment of proper indexing and MongoDB’s aggregation capabilities can further enhance the performance and complexity of time-based queries. Remember to always test your queries for performance and accuracy.