Sequelize - how to format the date for date types values

Sequelize has two data types that you can use to put date-related values:

• The DATEONLY type that converts to the SQL DATE format
• The DATE type that converts to the SQL DATETIME format

The two types use the ISO standard to format the values they send and receive from your SQL database.

Let’s see these formats with an example. Suppose you have an Invoices table in your database with the following column definitions:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int      | NO   | PRI | NULL    | auto_increment |
| invoiceDate | date     | YES  |     | NULL    |                |
| paymentDate | datetime | YES  |     | NULL    |                |
| amount      | int      | YES  |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

The table has only one row of data as shown below:

+----+-------------+---------------------+--------+
| id | invoiceDate | paymentDate         | amount |
+----+-------------+---------------------+--------+
|  1 | 2022-01-17  | 2022-01-17 04:33:12 |    300 |
+----+-------------+---------------------+--------+

Let’s query the table using Sequelize and see the format of invoiceDate and paymentDate values.

First, create a Sequelize model of the above table. The invoiceDate column uses the DATEONLY type while paymentDate column uses DATE type as shown below:

const Invoice = sequelize.define("Invoice", {
  invoiceDate: {
    type: Sequelize.DATEONLY,
  },
  paymentDate: {
    type: Sequelize.DATE,
  },
  amount: {
    type: Sequelize.INTEGER,
  },
},
{
  timestamps: false,
});

Next, you can query the table using Sequelize query methods.

Since we have only one row, let’s use the findByPk() method to make it easy:

const invoice = await Invoice.findByPk(1);

console.log(invoice.toJSON());

Here’s the value of the invoice object that’s logged to the console:

{
  id: 1,
  invoiceDate: '2022-01-17',
  paymentDate: 2022-01-17T04:33:12.000Z,
  amount: 300
}

From the above output, you can see that the invoiceDate value was returned as a string representing the date in the ISO format of YYYY-MM-DD.

On the other hand, the paymentDate value was returned as a JavaScript Date object using the ISO format of YYYY-MM-DDTHH:MM:SSZ.

These are the default formats used by Sequelize when dealing with date and time-related values.

When you need to change the format of these values, there are two methods that you can use:

• Using the sequelize.fn() method to call your database native date format function
• Using the attributes.column.get() method to format the date in JavaScript

Let’s learn how you can use these methods next.

Customizing Sequelize date format with Sequelize.fn()

The sequelize.fn() method is used to call a native database function to modify the way your query works.

For example, you can call the UPPER() function of MySQL and apply it to a column that you have as follows:

Model.findAll({
  attributes: {
    include: [
      [ sequelize.fn("UPPER", sequelize.col("username")), 'username_upper' ]
    ],
  },
});

The sequelize.fn() method must be enclosed in an array using the following format:

sequelize.fn(
  "function to call", 
  "first parameter", 
  "second parameter (if any)",
  "and the next parameter..."
)
, "the column alias"

The fn() method can only be called from the attributes or attributes.include property, and the properties are only supported by the findAll() method.

Using the method, you can call your database native date format function, such as DATE_FORMAT in MySQL or to_char in PostgreSQL.

Here’s an example of formatting the paymentDate column value from the Invoices table:

const invoices = await Invoice.findAll({
  attributes: {
    include: [
      "id",
      "invoiceDate",
      [
        sequelize.fn
        (
          "DATE_FORMAT", 
          sequelize.col("paymentDate"), 
          "%d-%m-%Y %H:%i:%s"
        ),
        "paymentDate",
      ],
      "amount",
    ],
  },
});

console.log(invoices[0].toJSON());

The output of the above console.log() call would be as follows:

{
  id: 1,
  invoiceDate: '2022-01-17',
  paymentDate: '17-01-2022 04:33:12',
  amount: 300
}

By calling the MySQL DATE_FORMAT function, the returned paymentDate value will be a string following the date format you specify as the second argument.

The above example uses %d-%m-%Y %H:%i:%s, but you can find the full list of available specifiers in MySQL date and time specifiers documentation.

Keep in mind that different SQL databases have different methods to use for formatting a date-time value. If you somehow switch to using PostgreSQL instead of MySQL later, then you need to change the function called by sequelize.fn().

Also, sequelize.fn() is not available when you want to query using other methods like findByPk().

Having to convert the query method you use just to format the date and time value is cumbersome. 😕

Don’t worry though, because the next method provides a solution that you can use with all Sequelize query methods and all SQL dialects.

Customizing Sequelize date format with attributes.column.get() method

Each Sequelize model attribute comes with both get() and set() methods that allow you to provide a custom getter and setter for the column.

Here’s the documentation of the methods from Sequelize website:

Since you’re focusing on formatting the date time values, you can use the get() function by specifying a get property in your model attribute.

Here’s an example of adding a get() property to the paymentDate attribute:

const Invoice = sequelize.define("Invoice", {
  invoiceDate: {
    type: Sequelize.DATEONLY,
  },
  paymentDate: {
    type: Sequelize.DATE,
    get: function() { // or use get(){ }
      return this.getDataValue('paymentDate')
        .toLocaleString('en-GB', { timeZone: 'UTC' });
    }
  },
  amount: {
    type: Sequelize.INTEGER,
  },
},
{
  timestamps: false,
});

Inside the get() function of the paymentDate attribute above, the value of the paymentDate column is retrieved using the this.getDataValue() method.

Because the value is a Date object, you can call the Date.toLocaleString() method on the returned value.

The en-GB locale time value uses day/month/year order and 24-hour time without AM/PM.

Let’s call the method findByPk() to retrieve the table row next:

const invoice = await Invoice.findByPk(1);

console.log(invoice.toJSON());

The returned values will be as follows. Notice how the paymentDate value format has been changed:

{
  paymentDate: '17/01/2022, 04:33:12', // formatted with en-GB locale
  id: 1,
  invoiceDate: '2022-01-17',
  amount: 300
}

Because you’re formatting the Date object value using JavaScript, the same get() method works for all SQL dialects without exceptions.

Furthermore, you can use any JavaScript date and time libraries like Luxon or Day.js to format your value.

Here’s an example of using Luxon to format the date-time to an fr locale string:

const { DateTime } = require("luxon");

// ...

get() {
  const dt = DateTime.fromJSDate(this.getDataValue('paymentDate'));
  return dt.setLocale('fr').toLocaleString(DateTime.DATETIME_FULL);
}

And that’s how you can format Sequelize date-time values using the get() method.

Feel free to use the method that you prefer for your project 😉