CODEWITHSUNDEEP
node.jssequelize.jssequelize-cli
nad34
nad34

Reputation: 393

How to get all records from the last 7 days sequelize

I am trying to get all records from a mysql Database with sequelize and I have tried following approaches:

shops.findAndCountAll({
  where: {
    createdAt: {
      [Op.gte]: moment().subtract(7, 'days').toDate()
    }
  }
})

and when I use this, I get the error: ReferenceError: moment is not defined

So I tried this approach:

shops.findAndCountAll({
  where: {
    createdAt: {
      [Op.gte]: Sequelize.literal('NOW() - INTERVAL "7d"'),
    }
  }
})

But I get the following error

code: 'ER_PARSE_ERROR',
    errno: 1064,
    sqlState: '42000',
    sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to 

your MySQL server version for the right syntax to use near '' at line 1",
        sql: "SELECT count(*) AS `count` FROM `shop` AS `shops` WHERE `shops`.`createdAt` >= NOW() - INTERVAL '7d';"
      },
      sql: "SELECT count(*) AS `count` FROM `shop` AS `shops` WHERE `shops`.`createdAt` >= NOW() - INTERVAL '7d';"
    }

How can I fix this issue. I do not mind which of the approaches I use, as long as I get it to work.

Thank you in advance

Upvotes: 8

Views: 5335

Answers (3)

Kalana
Kalana

Reputation: 6173

You could have get the result in the second approach by following this

const dayCount = '7d';

shops.findAndCountAll({
  where: {
    createdAt: {
      [Op.gte]: Sequelize.literal(`NOW() - INTERVAL '${dayCount}'`),
    }
  }
})

you can reuse this logic for any date ranges by changing dayCount param

Upvotes: -1

Manoj Rana
Manoj Rana

Reputation: 3460

Using DATE_ADD() or DATE_SUB()

SELECT * FROM Table_Name
WHERE connect_time >= DATE_ADD(CURDATE(),INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

Without those functions, you can also do

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() + INTERVAL -7 DAY);

or

SELECT * FROM Table_Name
WHERE connect_time >= (CURDATE() - INTERVAL 7 DAY);

Upvotes: 2

Valentin Roudge
Valentin Roudge

Reputation: 565

You are not importing moment ; therefore moment is not defined.

try

const moment = require('moment') //<es6

or

import moment from 'moment'

Upvotes: 10

Related Questions