CODEWITHSUNDEEP
mysqlnode.jsexpresssequelize.js
Muljayan
Muljayan

Reputation: 3886

How to fetch sequelize js records for today

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

Upvotes: 8

Views: 21587

Answers (6)

Teocci
Teocci

Reputation: 8996

The problem here is that you are comparing timestamps like '2019-05-27 11:40:48' and '2019-05-27 18:30:00'. So, if we compare them it will never give us a true result because even if it were the same day (27th of May) the time is different.

Here is 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

Upvotes: 16

Fahad Ayub
Fahad Ayub

Reputation: 51

Best way to get today records.

      const op = sequelize.Op;
      const moment = require('moment');
      const TODAY_START = moment().format('YYYY-MM-DD 00:00');
      const NOW = moment().format('YYYY-MM-DD 23:59');

      const todaysRecord = await OrdersModel.findAll({

                where: {
                    createdAt: {
                        [op.between]: [
                            TODAY_START,
                            NOW,
                        ]
                    }
                }
            });

Upvotes: 5

Ashok Kumar
Ashok Kumar

Reputation: 346

You can use Sequelize.literal:

    const { Op } = Sequelize;
    const options = {
        where: {}
    };

    options[Op.and] = [
        sequelize.where(Sequelize.literal('DATE(created) = CURDATE()'))            
    ] 

    const SUM = await OrdersModel.sum('price', options);
    console.log(SUM);

If there is no future date you can query like below,

    options[Op.and] = [
        sequelize.where(sequelize.col('created'), {
            [Op.gt]: Sequelize.literal('DATE_SUB(CURDATE(), INTERVAL 1 DAY)')
        })            
    ]
    const SUM = await OrdersModel.sum('price', options);
    console.log(SUM);

Upvotes: 0

ajaykumar mp
ajaykumar mp

Reputation: 546

We can also use [op.between], which will fetch data between two given date Range. So if we give today's Start time and current time it will give todays data.

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

where: {
createdAt: {
    [Op.between]: [START.toISOString(), NOW.toISOString()]
  }
}

Happy Coding...

Upvotes: 1

Khalid Skiod
Khalid Skiod

Reputation: 133

Using moment would be easier

const moment = require('moment');
const Op = require('sequelize').Op;
const SUM = await OrdersModel.sum('price', {
    where : {
                created_at : { [Op.gt] : moment().format('YYYY-MM-DD 00:00')},
                created_at : { [Op.lte] : moment().format('YYYY-MM-DD 23:59')}
            },
});
console.log(SUM);

Upvotes: 1

Senthil
Senthil

Reputation: 2246

Add DATE FUNCTION to do date comparision without considering time

const TODAY = new Date();
const SUM = await OrdersModel.sum('price', {
    where: {
      sequelize.fn('CURRENT_DATE'): {$eq:  sequelize.fn('date_trunc', 'day', sequelize.col('created'))}
    },
});
console.log(SUM);

Upvotes: 0

Related Questions