CODEWITHSUNDEEP
javascriptpostgresqldatesequelize.js
Tom
Tom

Reputation: 9167

How to perform date comparisons against postgres with 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.

I'm using sequelize 4.37 on top of a postgres 9.6 database.

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.)

So, I do this:

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

But I get this error:

Error: Invalid value { dateColumn: 2018-11-21T21:26:16.849Z }

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' }

Upvotes: 2

Views: 665

Answers (1)

Tom
Tom

Reputation: 9167

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.

Upvotes: 4

Related Questions