Query with date range using Sequelize request with Postgres in Node

Question

What I'm trying to do is to get rows in between two dates by using Sequelize ORM in Node.js. I'm using PostgreSQL. The problem is that the request that I'm making is interpreted incorrectly by Sequelize.

Here is the code that I'm using to make request

const dbresp = await Table.findAll({
  attributes: [...],
  where: {
    ...
    createdAt: {
       $between: [new Date(Date(startDate)), new Date(Date(endDate))],
       // same effect
       // $lte: new Date(startDate),
       // $gte: new Date(endDate),
    },
  },
  logging: console.log,
  raw: true,
  order: [['createdAt', 'ASC']],
  // limit: count,
});

By logging raw SQL request it is obvious that request is incorrect

SELECT ...
FROM "table" AS "table"
WHERE "table"."createdAt" = '2019-02-05 21:00:00.000 +00:00'
      "table"."createdAt" = '2019-02-05 21:00:00.000 +00:00'
ORDER BY "table"."createdAt" ASC;

What is a proper way to make such a request? Should I use a raw query?

I've googled this issue but no StackOverflow nither GitHub did help.

Answer 1

Ok, IDK what causes this issue but I fixed it by using Op object of Sequelize like this.

const Op = require('./models').Sequelize.Op;
const dbresp = await Table.findAll({
  attributes: [...],
  where: {
    ...
    createdAt: {
       [Op.between]: [startDate, endDate],
    },
  },
  logging: console.log,
  raw: true,
  order: [['createdAt', 'ASC']],
  // limit: count,
});

Seems like $between operator does not work

Answer 2

Try using the code shared below:

[Op.between]: [date1, date2]

Or the raw query:

SELECT ...
FROM table t
WHERE t.createdAt BETWEEN '2019-02-05' AND '2019-02-07'
ORDER BY t.createdAt ASC;