javascript - Sequelize returns null for Postgres 'timestamp with time zone' columns - Stack Overflow

admin管理员组

文章数量:1400424

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 binations 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' },

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 binations 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' },

• javascript
• node.js
• postgresql
• sequelize.js

Share Improve this question Follow edited Aug 27, 2019 at 19:34 Don Brody asked Aug 27, 2019 at 15:55 Don BrodyDon Brody 1,72722 gold badges2020 silver badges3030 bronze badges 3

• 1 Have you tried setting the logging parameter to true on the sequelize object to see what it sends? Then you can copy/paste the exact query in phAdmin to make sure it runs as you expect. – Victor P Commented Aug 27, 2019 at 16:18
• @VictorP thanks for the suggestion! I'm looking through the logs now. – Don Brody Commented Aug 27, 2019 at 16:34
• @VictorP the query executes and returns the data as expected in the Postgres query tool. – Don Brody Commented Aug 27, 2019 at 16:38

Add a ment  | 

4 Answers 4

Sorted by: Reset to default 2

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!

You can change DateStyle only into your session. After connect:

when use typeorm you can get PG Pool like this e add a listener on connect event. You can search how get pg driver in Sequelize and do it.

import { Pool } from 'pg';
import { PostgresDriver } from 'typeorm/driver/postgres/PostgresDriver';

if (connection.driver instanceof PostgresDriver) {
    const pool = connection.driver.master as Pool;
    pool.on('connect', (client) => {
      client.query('SET DATESTYLE = iso, mdy');
    });
}

So you can keep your database with current configuration e resolve you problem with dates.

Sequelize will add timestamps by default. Maybe you have to tell it not to use those defaults before you can use yours:

// remove defaults
createdAt: false,
updatedAt: false,
// declare mine
created_date: {
  type: Sequelize.DATE
  defaultValue: Sequelize.NOW,
  allowNull: false
},
updated_date: {
  type: Sequelize.DATE
  defaultValue: Sequelize.NOW,
  allowNull: true
},

For reference

https://sequelize/master/manual/models-definition.html#configuration

This is probably your problem and solution: https://github./brianc/node-postgres/issues/471

Sequelize works on top of pg, and pg does not know how to parse the non-iso date. Check your postgresql.conf file, what is your date format?

Try this: datestyle = 'iso, dmy'

(restart service to apply)

本文标签： javascriptSequelize returns null for Postgres 39timestamp with time zone39 columnsStack Overflow