How To Use Sequelize Date with Example

in this article, We’ll explore how to handle date and time data with Sequelize effectively. We’ll discuss various date and time-related tasks, such as storing timestamps, performing date calculations, and filtering data based on date ranges.

The Sequelize is a popular Object-Relational Mapping (ORM) library that simplifies database interactions, including dealing with date and time data. The sequelize supports various database management systems, including PostgreSQL, MySQL, SQLite, and MSSQL.

We’ll also cover sequelize data types, how to query in sequelize, and how to manipulate date using sequelize.

Sequelize Date Data Types

Sequelize provides many data types to handle managing dates and times. There are the following most commonly used date data types:

• DATE: This data type represents a date (without time) and is stored in the database as a DATE type.
• DATETIME: This data type includes both date and time. It’s stored in the database as DATETIME or TIMESTAMP based on the database system.
• TIME: It represents a time of day. It’s stored as a TIME or INTERVAL in the database.
• NOW: You can use this data type to inserts the current date and time when a record is created or updated.

I have already covered below sequelize tutorial:

• Add multiple rows using Sequelize bulkCreate() method
• Delete Your Table Data by ID using Sequelize

How to Define Date Fields in Models

Let’s define the date field in our sequelize models. You can define date and time fields using Sequelize data types such as DATE, DATEONLY, TIME, DATETIME, and NOW:

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('mydb', 'myuser', 'mypassword', {
  dialect: 'mysql',
  host: 'localhost',
});
const Employee = sequelize.define('Employee', {
  dob: {
    type: Sequelize.DATE,
	allowNull: false
  },
  joiningDate: {
    type: Sequelize.DATETIME,
  },
});

How To Define Default Values

You can set default values for date fields by using defaultValue attribute. Let’s set joiningDate to the current date and time by default:

const Employee = sequelize.define('Employee', {
  joiningDate: {
    type: Sequelize.DATE,
    defaultValue: Sequelize.NOW,
  },
  // ...
});

Sorting Records by Date

You can sort records based on date fields by specifying the order option in your queries. Let’s retrieve employee sorted by their birthdate in ascending order:

const Employee = await Employee.findAll({
  order: [['dob', 'ASC']],
});

Date Comparisons

The Sequelize allows you to perform date-based comparisons using built-in operators like Op.gte (greater than or equal), Op.lt (less than), and others. You can find all employees with joiningDate greater than today:

const { Op } = require('sequelize');
Employee.findAll({
  where: {
    joiningDate: {
      [Op.gt]: new Date(),
    },
  },
});

Formatting Dates

You can format dates for display using libraries like moment.js. Here’s how you can format a date field in a Sequelize result:

const moment = require('moment');

Employee.findAll().then((emps) => {
  emps.forEach((emp) => {
    console.log(`Employee name: ${emp.name}`);
    console.log(`Joining Date: ${moment(emp.joiningDate).format('YYYY-MM-DD')}`);
  });
});

Querying with Dates

Sequelize offers a number of ways to query documents using date fields. You can perform common operations like filtering records based on date ranges, sorting by date, and calculating the difference between dates.

Let’s find records with a specific date using the sequelize.where method.

User.findAll({
  where: sequelize.where(sequelize.fn('DATE', sequelize.col('dob')), '=', '2001-10-01'),
});

How To Find Records Within a Date Range

We can find records between two dates in sequelize using the Op.between() method.

User.findAll({
  where: {
    dob: {
      [Op.between]: ['2001-10-01', '2023-10-01'],
    },
  },
});

Date Manipulation Using Sequelize

Sequelize provides date manipulation method as well to modify date. The sequelize.fn. is used to manipulation of date in sequelize:

User.findAll({
  attributes: [
    [sequelize.fn('YEAR', sequelize.col('dob')), 'birthYear'],
    [sequelize.fn('MONTH', sequelize.col('dob')), 'birthMonth'],
  ],
});

Conclusion:

We have covered the basics of sequelize date times. You can define sequelize models with proper data types. We have lo queried to find some data from sequelize database and also manipulated data.