Sequelize.js: Select Rows by Last Day, Last Week, Last Month, and Other Date Ranges

Introduction

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more. In this guide, we’ll explore how to query for data over different date ranges, such as the last day, week, and month, using Sequelize. Fetching data by date range is a common requirement for many applications, such as generating reports, dashboards, and filtering records for analytics.

Setting Up The Model

Before diving into the queries, we’ll assume you have a Sequelize model called Event with a createdAt DateTime field. Here is a basic setup:

const Event = sequelize.define('event', {
  //... other fields ...
  createdAt: {
    type: Sequelize.DATE,
    allowNull: false,
  },
  // ... other fields ...
});

Select Rows by Last Day

To select rows from the last 24 hours, you would create a query like this:

const Op = Sequelize.Op;
const oneDayAgo = new Date(new Date() - 24 * 60 * 60 * 1000);

Event.findAll({
  where: {
    createdAt: {
      [Op.gt]: oneDayAgo
    }
  }
});

This uses the greater than (Op.gt) operator to find all events created after the date stored in oneDayAgo.

Select Rows by Last Week

To fetch records from the last week, we adjust our time calculation:

const oneWeekAgo = new Date(new Date() - 7 * 24 * 60 * 60 * 1000);

Event.findAll({
  where: {
    createdAt: {
      [Op.gt]: oneWeekAgo
    }
  }
});

This will retrieve all events created in the past week.

Select Rows by Last Month

Selecting rows from the last month is similar, but we take care to handle the varying number of days in a month:

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

Event.findAll({
  where: {
    createdAt: {
      [Op.gt]: oneMonthAgo
    }
  }
});

Here we use the setMonth() method to get the date one month ago from the current date.

Selecting Rows within Arbitrary Date Ranges

What if you need to select events within a date range that does not conform to the last day, week, or month? Sequelize allows you to specify an arbitrary range using the between operator:

const startDate = new Date('2023-01-01');
const endDate = new Date('2023-01-07');

Event.findAll({
  where: {
    createdAt: {
      [Op.between]: [startDate, endDate]
    }
  }
});

This will select all events created between January 1, 2023, and January 7, 2023.

Advanced Queries

For more complex scenarios, you might want to include time grouping and aggregation. For example, selecting the number of events created each day over the last week:

const Sequelize = require('sequelize');
const { Op } = require('sequelize');
const oneWeekAgo = new Date(new Date() - 7 * 24 * 60 * 60 * 1000);

Event.findAll({
  attributes: [
    [Sequelize.fn('DATE', Sequelize.col('createdAt')), 'date'],
    [Sequelize.fn('COUNT', Sequelize.col('createdAt')), 'count']
  ],
  where: {
    createdAt: {
      [Op.gt]: oneWeekAgo
    }
  },
  group: 'date',
  order: [[Sequelize.fn('DATE', Sequelize.col('createdAt')), 'ASC']],
});

Here we’re using Sequelize’s function to group the results by date and count the number of events on each date.

Conclusion

Selecting rows by date range is a powerful feature in Sequelize that can greatly simplify the process of generating time-based reports and analytics. Starting with simple greater than queries and moving to more complex grouping and aggregation, Sequelize gives you the tools to handle most time-based query requirements with ease. With the basics covered in this tutorial, you’ll be ready to start implementing more sophisticated time-based query logic in your own Sequelize applications.