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.

Number16BusShelter · Accepted Answer · 2019-02-05 21:59:30Z

6

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

answered Feb 5, 2019 at 21:59

Number16BusShelter

6902 gold badges9 silver badges17 bronze badges

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

Comments

Rishikesh Chandra · Accepted Answer · 2019-02-05 22:01:06Z

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;

edited Feb 5, 2019 at 22:01

answered Feb 5, 2019 at 21:56

Rishikesh Chandra

6919 silver badges25 bronze badges

1 Comment

Number16BusShelter Over a year ago

Yeah, this will probably work. But I'd like to figure out what causes this issue :)

Etornam · Accepted Answer · 2020-02-10 16:58:12Z

1

This should work. Sequelize allows you to create aliases. https://sequelize.org/v5/manual/querying.html

const Sequelize = require('sequelize');
const op = Sequelize.Op;
const operatorsAliases = {
    $between: op.between, //create an alias for Op.between
}
const connection = new Sequelize(db, user, pass, { operatorsAliases })


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,
});

edited Feb 10, 2020 at 16:58

answered Feb 10, 2020 at 16:20

Etornam

5231 gold badge3 silver badges10 bronze badges

Collectives™ on Stack Overflow

Query with date range using Sequelize request with Postgres in Node

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related