如何使用Sequelize对Postgres执行日期比较 [英] How to perform date comparisons against postgres with sequelize
问题描述
我想删除所有日期早于20分钟的记录.我提供的裸javascript Date
对象作为比较值,Postgres(或Sequelize)不满意.
I want to delete all records with dates before 20 minutes ago. Postgres (or Sequelize) is not satisfied with the bare javascript Date
object I provide as the comparison value.
我在postgres 9.6数据库上使用sequelize 4.37.
I'm using sequelize 4.37 on top of a postgres 9.6 database.
有问题的列用 type:Sequelize.DATE
声明,研究表明该列等效于 TIMESTAMP WITH TIME ZONE
:完整的日期和时间,微秒精度,时区指示符.(这也是我使用psql CLI工具描述表时看到的.)
The column in question was declared with type: Sequelize.DATE
, which research suggests is equivalent to TIMESTAMP WITH TIME ZONE
: a full date and time with microsecond precision and a timezone signifier. (That is also what I see when I use the psql CLI tool to describe the table.)
所以,我这样做:
const Sequelize = require('sequelize')
const { SomeModel } = require('../models.js')
// calculate 20 minutes ago
async function deleteStuff() {
const deletionCutoff = new Date()
deletionCutoff.setMinutes( deletionCutoff.getMinutes() - 20 )
await SomeModel.destroy({
where: {
[ Sequelize.Op.lt ]: { dateColumn: deletionCutoff }
}
})
但是我得到这个错误:
Error: Invalid value { dateColumn: 2018-11-21T21:26:16.849Z }
文档建议我应该能够提供裸露的JavaScript日期或ISO8601字符串,但两者均会引发相同的 Invalid Value
错误.唯一的区别是,如果我传递字符串,则错误将在值周围显示单引号:
The docs suggest I should be able to provide either a bare javascript Date, or an ISO8601 string, but both throw the same Invalid Value
error. The only difference is that, if I pass a string, the error shows single quotes around the value:
// error when dateColumn: deletionCutoff.toISOString()
Error: Invalid value { dateColumn: '2018-11-21T21:26:16.849Z' }
推荐答案
嗯,这很尴尬.我错误地构造了 where
子句.
Well, this is pretty embarrassing. I structured the where
clause incorrectly.
// BAD CODE
await SomeModel.destroy({
where: {
[ Sequelize.Op.lt ]: {
dateColumn: deletionCutoff
}
}
})
// GOOD CODE
await SomeModel.destroy({
where: {
dateColumn: {
[ Sequelize.Op.lt ]: deletionCutoff
}
}
})
也许我应该删除问题.也许不是,我得到的错误可能会更有帮助.
Maybe I should delete the question. Maybe not -- the error I got probably could be more helpful.
这篇关于如何使用Sequelize对Postgres执行日期比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!