Query by Date Range for a column field in Sequelize

Question

I'm trying to query a database with Sequelize to get items that were created between a certain date range. I used the $between operator but I don't seem to be getting anything.

{ where: {"createdAt":{"$between":["2018-03-31T21:00:00.000Z","2018-05-30T05:23:59.007Z"]}} }

Can anyone help with how I can achieve this?

Solution 1

I also had the same problem, I was getting empty array in response. The problem got fixed using :

const Op = Sequelize.Op;

and then using [Op.between] as shown below:

where: {
  createdAt: {
    [Op.between]: ["2018-07-08T14:06:48.000Z", "2019-10-08T22:33:54.000Z"]
  }
}

Hope it helps :)

Solution 2

$between syntax seems to be right. There are no issues with the way you used. I tried to replicate with the following query

  model.findAll({
    where: {
      created_at: { 
        "$between": ["2018-03-31T21:00:00.000Z","2018-05-30T05:23:59.007Z"]
      }
    }
  })

The only change is, I use created_at instead of createdAt. Make sure that your column name is right. If it is not, it should have thrown SequelizeDatabaseError. Look for it.

If everything else is right, then you might not be having data in that date range :)

Comments

[proton]

Thanks. It's funny it's not working on my end. I've tried a larger date range and I keep getting an empty value. I'll go through my code again.

[Gareth Oakley]

Ideally you should also be using the symbol based operators like [Op.between]: docs.sequelizejs.com/manual/tutorial/querying.html#operators (it's better from a security perspective)

[Ali SadeghipourKorabaslo]

ahhh I looking for something like select * FROM table where "2021-04-13" BETWEEN start_date_column and end_date_column any idea?