Sequelize returns null for Postgres ‘timestamp with time zone’ columns

I’m new to Sequelize and am having trouble getting timestamp data from a Postgres database. Everything I have tried so far returns null for all timestamp fields.

Below are the Postgres definitions for the columns in question:

created_date timestamp with time zone NOT NULL DEFAULT current_timestamp,
updated_date timestamp with time zone NOT NULL DEFAULT current_timestamp,

These are all the different ways I’ve attempted to define the relevant portion of the model in Node.js (tried the same things for updated_date):

created_date: {
  type: Sequelize.DATE
  defaultValue: Sequelize.NOW,
  allowNull: false
},
created_date: {
  type: 'TIMESTAMP',
  defaultValue: sequelize.literal('CURRENT_TIMESTAMP'),
  allowNull: false
},
created_date: {
  type: 'timestamp with time zone',
  defaultValue: sequelize.literal('current_timestamp'),
  allowNull: false
},
created_date: {
  type: Sequelize.STRING,
},

For all combinations the Sequelize timestamp attribute has been set to false (because we are handling them manually). I’ve tried to change this value to true as well with no success.

Lastly, the query is structured like so (notice the two different ways I’ve attempted to access the columns below):

data = await <MyModel>.findAll({
  order: [['created_date', 'DESC']],
  attributes: [
    'id',
    ['created_date', 'createdDate'],
    [models.sequelize.col('<my_table>.updated_date'), 'updatedDate'],
    ...
  ],
  ...
});

All attempts have returned null for both timestamp values from the database. I have verified that the timestamp values in the database do exist and are not null. And, all other database values are being returned as expected.

Using Sequelize v5.9.4

Any help tracking down the root cause of this issue will be greatly appreciated. I’ve spent way too many hours trying to figure this one out. Thanks in advance!

Edit – More information that may be relevant:

The following is the metadata that is returned from running a raw query through Sequelize:

 Field {
   name: 'created_date',
   tableID: XXXXX,
   columnID: XX,
   dataTypeID: 1184,
   dataTypeSize: 8,
   dataTypeModifier: -1,
   format: 'text' },
 Field {
   name: 'updated_date',
   tableID: XXXXX,
   columnID: XX,
   dataTypeID: 1184,
   dataTypeSize: 8,
   dataTypeModifier: -1,
   format: 'text' },

Answer

I’m posting the solution my team landed on after a few days of head scratching and pouring over the Sequelize documentation. It requires casting the timestamp to a string and using moment to handle the date and time manually in JavaScript. I’m not posting the moment portion of the fix here, but the way we cast the timestamp to a string is as follows:

const data = await <MyModel>.findAll({
  order: [['created_date', 'DESC']],
  attributes: [
    'id',
    [sequelize.cast(sequelize.col('<my_table>.created_date'), 'String'), 'createdDate'],
    [sequelize.cast(sequelize.col('<my_table>.updated_date'), 'String'), 'updatedDate'],
    ...
  ],
  ...
});

Hopefully this saves someone else some time in the future!

Advertisement

Answer