使用带有Postgres的Sequelize请求在Node中查询日期范围 [英] Query with date range using Sequelize request with Postgres in Node

问题描述

我想做的是通过使用Node.js中的Sequelize ORM在两个日期之间获取行.我正在使用PostgreSQL.问题是Sequelize错误地解释了我正在发出的请求.

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

通过记录原始SQL请求，很明显该请求是不正确的

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?

我已经搜索了这个问题，但是没有StackOverflow以及其他GitHub都没有帮助.

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

推荐答案

好的，IDK会导致此问题，但是我通过使用Sequelize的Op对象来解决了此问题.

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

似乎 $ between 运算符不起作用

这篇关于使用带有Postgres的Sequelize请求在Node中查询日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！