javascript - How can I use datediff of mysql with sequelizejs - Stack Overflow

admin管理员组

文章数量:1341475

I'm trying to implement this SQL to my Sequelize

SELECT file_id FROM table WHERE datediff(curdate(),create_date) > 5;

   Here is my Sequelize

   findOverPeriodFile: function () {
    return table.findAll({
        where:{
            60: {lt: Sequelize.fn('')}
        }
    });
}

Im new to Seuelize and I have tried to search Google, but it doesn't help. Does anyone have an answer to this question? I don't know what to put in the WHERE statement. Sorry for my bad English.

I'm trying to implement this SQL to my Sequelize

SELECT file_id FROM table WHERE datediff(curdate(),create_date) > 5;

   Here is my Sequelize

   findOverPeriodFile: function () {
    return table.findAll({
        where:{
            60: {lt: Sequelize.fn('')}
        }
    });
}

Im new to Seuelize and I have tried to search Google, but it doesn't help. Does anyone have an answer to this question? I don't know what to put in the WHERE statement. Sorry for my bad English.

• javascript
• node.js
• sequelize.js

Share Follow edited Oct 14, 2019 at 9:40 szczepaniakdominik 47277 silver badges2525 bronze badges asked Mar 5, 2018 at 3:00 Nguyen Hoang VuNguyen Hoang Vu 86344 gold badges1818 silver badges3838 bronze badges

Add a ment  | 

5 Answers 5

Sorted by: Reset to default 8

Here you go :

You can generate WHERE datediff(curdate(),create_date) > 5; by using :

{ 
    where: sequelize.where(sequelize.fn('datediff', sequelize.fn("NOW") , sequelize.col('create_date')), {
        $gt : 5 // OR [Op.gt] : 5
    })
}

---

For adding more condition

{
    where: {
        $and : [
            sequelize.where(sequelize.fn('datediff', sequelize.fn("NOW") , sequelize.col('create_date')), {
                $gt : 5 // OR [Op.gt] : 5
            }) ,
            { status : 'mystatus' }
        ]
    }
} 

sequelize.where(
    sequelize.fn(
        'timestampdiff', 
         sequelize.literal("minute"),
         sequelize.col('updatedAt'),
         sequelize.literal('CURRENT_TIMESTAMP')
    ), 
    {
        [Op.gte] : ACCEPTION_TIMEOUT
    }
)

If someone like me will be searching for a way to find date or time difference in sequelize (not only in days), timestampdiff is a working way to do that.

sequelize.literal("minute") — is a unit of time. More on that here

sequelize.col('updatedAt') — column in the database (datetime)

sequelize.literal('CURRENT_TIMESTAMP') — sequelize literal for current time

{
   [Op.gte] : ACCEPTION_TIMEOUT
}

And this one is your SELECT condition for time matters

sequelize.where(sequelize.fn('datediff', sequelize.literal("day"), sequelize.col('Your Column date'), sequelize.fn("getdate")), {
          [Op.gt]: 0
        })

for Microsoft SQL Server (MSSQL) This one worked for me. Thanks to @Vivek Doshi

let pending = await Table.findAll({
        where: {
            [Op.and] : [
                Sequelize.where(Sequelize.fn('datediff',  Sequelize.col('DD'), Sequelize.col('createdAt'), Sequelize.fn("GETDATE")), {
                    [Op.lt] : 1 
                }) ,
                { is_processed : '0' }
            ]
        }
    } );

This will generate

SELECT * FROM [table] AS [table] 
WHERE (datediff(DD, [createdAt], GETDATE()) < 1 AND [table].[is_processed] = 0);

Which is exactly what I wanted

where: {
        [Op.and]: [
          sequelize.where(
            sequelize.fn(
              "datediff",
              sequelize.fn("NOW"),
              sequelize.col("lastSubmitted") // your column name 
            ),
            {
              [Op.gt]: 1,
            }
          ),
          {status:"active"},
        ],
}

Remeber to import OP from Sequelize and it's not a default export.

This should do the work, I was using $and and $gt before, but they didn't work.

本文标签： javascriptHow can I use datediff of mysql with sequelizejsStack Overflow