Sequelize, Postgres, and Dates

I ran into the exact same problem while working on two different API’s about two years apart, with tech stacks that I had thought of as being significantly different. But both applications used Node.js, PostgreSQL, and Sequelize, and both applications had timezone issues.

While working on the second API, I found myself on a GitHub ticket from Sequelize that seemed familiar — How to configure column without timezone in timestamp? #2572 — but I really felt deja vu when I hit the exact same blog post with the solution that I had used two years prior: Working with PostgreSQL’s timestamp without timezone in Node. The core issue is that Sequelize seems to have poor-to-no-support for the timestamp without timezone column type in Postgres.

The blog post Working with PostgreSQL’s timestamp without timezone in Node offers two solutions: 1) disable date parsing by your application entirely and pull all such fields as strings, or 2) force UTC conversion when pulling the fields from your database. I’ve now done both.

For the first API, their database had many tables storing dates in different formats. Rather than force consistent handling of dates, which would have involved a number of database migrations and refactoring, the team’s preference was to handle problems on an ad hoc basis. Their application used quite a bit of hand-written SQL, and I believe I was able to work around most issues by including AT TIME ZONE 'UTC' in the queries.

For the second API, somewhere early in the application bootstrap process, I included a couple lines of code to pull date types as strings:

const pg = require('pg');

pg.types.setTypeParser(1114, str => str);

In the Sequelize models, I created getter functions to manually convert the fields as expected, similar to the snippet below. However, I slightly modified the contents of get to be a reusable utility function.

const moment = require('moment');
 
createdAt: {
    type: DataTypes.STRING,
    field: 'created_at',
    get: function () {
      const rawValue = this.getDataValue('createdAt');
      return moment.utc(rawValue).format();
    }
  },