Incorrect date format between Sequelize and Postgres

Question

I have a probleme with postgre results and Sequelize expected results. The same query, but results differents between Sequelize and Postgre, I think is something about timezome. My timezone is Europe/Paris

My query is used to retrieve all summed prices for the week :

SELECT date_trunc('day', "date") AS "date_alias", sum("cost") AS "total"
FROM "finance" AS "Finance" 
WHERE "Finance"."date" 
BETWEEN '2022-02-13 23:00:00.000 +00:00' AND '2022-02-19 23:00:00.000 +00:00' 
GROUP BY "date_alias"

Postgre result look like :

Week data look like:

Sequelize returned results:

Executing (default): SELECT date_trunc('day', "date") AS "date_alias", sum("cost") AS "total" FROM "finance" AS "Finance" WHERE "Finance"."date" BETWEEN '2022-02-13 23:00:00.000 +00:00' AND '2022-02-19 23:00:00.000 +00:00' GROUP BY "date_alias";
[
  Finance {
    dataValues: { date_alias: 2022-02-14T00:00:00.000Z, total: '76' },
    _previousDataValues: { date_alias: 2022-02-14T00:00:00.000Z, total: '76' },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  },
  Finance {
    dataValues: { date_alias: 2022-02-17T00:00:00.000Z, total: '14' },
    _previousDataValues: { date_alias: 2022-02-17T00:00:00.000Z, total: '14' },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  },
  Finance {
    dataValues: { date_alias: 2022-02-18T00:00:00.000Z, total: '10' },
    _previousDataValues: { date_alias: 2022-02-18T00:00:00.000Z, total: '10' },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  }
]

In JSON :

[
  {
    "date_alias": "2022-02-13T00:00:00.000Z",
    "total": "10"
  },
  {
    "date_alias": "2022-02-14T00:00:00.000Z",
    "total": "76"
  },
  {
    "date_alias": "2022-02-17T00:00:00.000Z",
    "total": "14"
  },
  {
    "date_alias": "2022-02-18T00:00:00.000Z",
    "total": "10"
  }
]

My controller :

const { Op, Sequelize } = require('sequelize')
const {startOfWeek, lastDayOfWeek, startOfMonth, lastDayOfMonth, lastDayOfYear, startOfYear } = require('date-fns');

module.exports = {
    async getAll(req, res) {
      try {
        
        //default week
        let startedDate = startOfWeek(new Date(), { weekStartsOn: 1 });
        let endDate     = lastDayOfWeek(new Date(), { weekStartsOn: 1 });
        let periodParam = 'day';

        if(req.params.period && req.params.period === "month") {
          startedDate = startOfMonth(new Date());
          endDate     = lastDayOfMonth(new Date());
          periodParam = 'month';
        }
        if(req.params.period && req.params.period === "year") {
          startedDate = startOfYear(new Date());
          endDate     = lastDayOfYear(new Date());
          periodParam = 'year';
        }

        let options = {
          ...(req.params.period && { attributes: [
            [ Sequelize.fn('date_trunc', periodParam, Sequelize.col('date')), `date_alias`],
            [ Sequelize.fn('sum', Sequelize.col('cost')), 'total']
          ]}),
          ...(req.params.period &&  {group: ['date_alias']}),
          where: {
            date: {
              [Op.between] : [startedDate, endDate],
              //[Op.gte]: startedDate,
              //[Op.lt]: endDate,
            }
          },
          ...(!req.params.period && {order: [
            ['date', 'ASC']
          ]}),
          ...(!req.params.period && {include: {
              association: 'taxonomies',
              attributes: ['id'],
              through: {
                attributes: []
              }    
            }
          }),
        };

        const data = await req.Model.findAll(options);
        res.json(data);
      } catch (err) {
        res.status(500).send(err);
      }
    },
}

Why Sequelize return 2022-02-13T00:00:00.000Z instead of 2022-02-13T23:00:00.000Z ? My expected result should be :

[
  {
    "date_alias": "2022-02-13T23:00:00.000Z",
    "total": "43"
  },
  {
    "date_alias": "2022-02-14T23:00:00.000Z",
    "total": "43"
  },
  {
    "date_alias": "2022-02-17T23:00:00.000Z",
    "total": "14"
  },
  {
    "date_alias": "2022-02-18T023:00:00.000Z",
    "total": "10"
  }
]

florian_drupal · Accepted Answer · 2022-02-16 08:37:23Z

1

I found the solution, I need to configure sequelize like this with dialectOptions

const {Sequelize} = require('sequelize');

const sequelize = new Sequelize(process.env.PG_URL, {
    logging: true,
    dialectOptions: {
        useUTC: false, //for reading from database
        dateStrings: true,
        typeCast: function (field, next) { // for reading from database
          if (field.type === 'DATETIME') {
            return field.string()
          }
            return next()
          },
      },
    timezone: 'Europe/Paris',
});

module.exports = sequelize;

answered Feb 16, 2022 at 8:37

florian_drupal

1579 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Incorrect date format between Sequelize and Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related