[Fixed] How to fetch sequelize js records for today

Issue

I have a table which looks similar to the below table. I am trying to find the sum of all prices for TODAY.

| id| price |       created        |
|---|-------|----------------------|
| 0 |  500  | 2018-04-02 11:40:48  |
| 1 | 2000  | 2018-04-02 11:40:48  |
| 2 | 4000  | 2018-07-02 11:40:48  |

The below code is what i came up with but it doesn’t seem to work.

const TODAY = new Date();
const SUM = await OrdersModel.sum('price', {
    where: {
      created: TODAY,
    },
});
console.log(SUM);

Value of SUM is 0 even though there are entries for today. I also tried the following but it too didn’t work.

const TODAY = new Date();
const SUM = await OrdersModel.sum('price', {
    where: {
      created: Sequelize.DATE(TODAY),
    },
});
console.log(SUM);

The SQL statement queried on the terminal is as follows.

Executing (default): SELECT sum(`price`) AS `sum` FROM `orders` AS `orders` WHERE `orders`.`created` = ‘2019-05-27 18:30:00’;

Solution

What is going on here is that you are comparing exact timestamps like '2019-05-27 11:40:48' equal to '2019-05-27 18:30:00'. So this comparison will never give you a result because even if it’s the same day (27th of May) but the time is different.

So here you have a possible solution.

const Op = Sequelize.Op;
const TODAY_START = new Date().setHours(0, 0, 0, 0);
const NOW = new Date();

const SUM = await OrdersModel.sum('price', {
    where: {
      created: { 
        [Op.gt]: TODAY_START,
        [Op.lt]: NOW
      },
    },
 });
 console.log(SUM);

You need to create a query like this: created < [NOW] AND created > [TODAY_START] Why? because you will get the sum of all the prices registered after NOW. This code will help you also to get the total of a range of dates.

Alternative for PostgreSQL

Notice that PostgreSQL allows you to truncate to specific intervals. So, you can call the sequelize.fn() method to use create a query that calls ‘date_trunc’ you can read more in this link. Like this:

const SUM = await OrdersModel.sum('price', {
    where: {
      sequelize.fn('CURRENT_DATE'): {
        [Op.eq]:  sequelize.fn('date_trunc', 'day', sequelize.col('created'))
      }
    },
});
console.log(SUM);

Also remember to update to the latest version:

npm i [email protected] --s