在 Node 中使用带有 Postgres 的 Sequelize 请求查询日期范围 [英] 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 运算符不起作用

Seems like $between operator does not work

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