Sling Academy
Home/Node.js/Dates, Timestamps, and Timespans in Sequelize.js

Dates, Timestamps, and Timespans in Sequelize.js

Last updated: December 29, 2023

Introduction

Sequelize is a powerful Node.js ORM for relational databases such as PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. It provides straightforward methods for managing dates, timestamps, and timespans, crucial for applications that handle scheduling, events, or logging. This article introduces Sequelize’s mechanisms for handling these date-related concepts and provides practical examples to help you manage them effectively in your applications.

Understanding Dates

In Sequelize, date values are usually represented using JavaScript’s built-in Date object. When defining a model with a date column, you can specify its type using Sequelize.DATE. Here’s a simple example of a model with a date field:

const { Model, DataTypes } = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  dialect: 'mysql',
});

class Event extends Model {}
Event.init({
  date: {
    type: DataTypes.DATE,
    allowNull: false,
  },
}, { sequelize, modelName: 'event' });

In the above code snippet, we define an Event model with a date field. By default, Sequelize translates this into a DATETIME column in the database, which stores both date and time values.

Handling Timestamps

Timestamps are specific points in time, usually stored with information about both the date and time of an event. They are essential for tracking when records were created or updated. Sequelize automatically adds createdAt and updatedAt fields to your models unless explicitly told not to. Here’s how to disable automatic timestamps:

class LogEntry extends Model {}
LogEntry.init({
  message: DataTypes.STRING
}, {
  sequelize,
  modelName: 'logEntry',
  timestamps: false
});

Working with Timespans

Timespans represent a duration of time rather than a specific moment. Sequelize does not have a specific data type for timespans; instead, you can typically use integers to represent the duration in your preferred unit (seconds, minutes, hours, etc.). For example, to represent the length of an event in minutes:

class Course extends Model {}
Course.init({
  lengthInMinutes: {
    type: DataTypes.INTEGER,
    allowNull: false,
  },
}, { sequelize, modelName: 'course' });

Advanced Date Handling

For more advanced date and time handling, such as setting default values, using the NOW function, or handling timezones, sequelize provides additional functionalities. For instance, setting a default value to the current timestamp:

Event.init({
  createdAt: {
    type: DataTypes.DATE,
    defaultValue: Sequelize.NOW,
  },
}, { sequelize, modelName: 'event' });

Sequelize also offers ways to handle timezone conversions, which can be set globally on a Sequelize instance, or individually per query.

Querying Date Ranges

One of the more common operations involving dates in Sequelize is querying a range of dates. Here’s an example of how to find events occurring between two dates:

const startDate = new Date(2020, 0, 1);
const endDate = new Date(2020, 11, 31);
Event.findAll({
  where: {
    date: {
      [Sequelize.Op.between]: [startDate, endDate]
    }
  }
});

This method utilizes the between operator to specify the start and end points of the date range.

Conclusion

Managing dates, timestamps, and timespans with Sequelize is intuitively integrated with JavaScript’s Date object and operations, allowing developers to handle complex scheduling and time-tracking tasks efficiently. Remember that handling timezones can be tricky, so make sure your application settings are consistent with your database’s timezone. With a proper understanding and utilization of Sequelize’s date-related features, you can build robust, time-aware applications.

Next Article: How to Implement Pagination in Sequelize.js

Previous Article: createdAt and updatedAt fields in Sequelize.js

Series: Sequelize.js Tutorials

Node.js

You May Also Like

  • NestJS: How to create cursor-based pagination (2 examples)
  • Cursor-Based Pagination in SequelizeJS: Practical Examples
  • MongooseJS: Cursor-Based Pagination Examples
  • Node.js: How to get location from IP address (3 approaches)
  • SequelizeJS: How to reset auto-increment ID after deleting records
  • SequelizeJS: Grouping Results by Multiple Columns
  • NestJS: Using Faker.js to populate database (for testing)
  • NodeJS: Search and download images by keyword from Unsplash API
  • NestJS: Generate N random users using Faker.js
  • Sequelize Upsert: How to insert or update a record in one query
  • NodeJS: Declaring types when using dotenv with TypeScript
  • Using ExpressJS and Multer with TypeScript
  • NodeJS: Link to static assets (JS, CSS) in Pug templates
  • NodeJS: How to use mixins in Pug templates
  • NodeJS: Displaying images and links in Pug templates
  • ExpressJS + Pug: How to use loops to render array data
  • ExpressJS: Using MORGAN to Log HTTP Requests
  • NodeJS: Using express-fileupload to simply upload files
  • ExpressJS: How to render JSON in Pug templates