Compare Timestamp with date in sequelize query

Question

I have createdAt column which stores value as "2018-11-07 15:03:16.532+00". I want to write query like select * from table_name where createdAt = input_date, where myinput_date is only date value like 2018-11-07. How do i write this query using Sequelize?

Brother

Answer 1

try using the sequelize.fn() argument, you can follow example code below.

TableName.findAll({
  where: sequelize.where(sequelize.fn('date', sequelize.col('createdAt')), '=', '2018-11-07')
})

Gustavogustavus

Perfect! Thank you – Chemical

Thank you. Save my day – Sibilla

Thank you so much, this working perfectly! It's still valid in 2023 and the best way to get data by year from timestamp column – Corporative

Thank you. Awesome! Your tip solved my problem. – Episcopate

Answer 2

The accepted answer should work fine, but if we have multiple conditions in where I am using this -

const cashRegisterData = await cashRegisterMain.findAll({
            attributes: ['crmid', 'dateCreated', 'startDate', 'endDate', 'startAmount', 'finalAmount', 'status'],
            where: {
                createdBy: userId,
                [Op.and]: [
                    sequelize.where(sequelize.fn('date', sequelize.col('dateCreated')), '>=', fromDate),
                    sequelize.where(sequelize.fn('date', sequelize.col('dateCreated')), '<=', toDate),
                ]
            },
            order: [['dateCreated', 'DESC']],
        });

Generated Query:

SELECT `crmid`, `dateCreated`, `startDate`, `endDate`, `startAmount`, `finalAmount`, `status` FROM `cashRegisterMain` AS `cashRegisterMain` WHERE (date(`dateCreated`) >= '2020-11-20' AND date(`dateCreated`) <= '2020-11-20') AND `cashRegisterMain`.`createdBy` = 1 ORDER BY `cashRegisterMain`.`dateCreated` DESC;

Joyajoyan

Answer 3

Both answers above work fine. They both use sequelize.fn() which isn't mandatory. I don't have any issue with using sequelize.fn() but I think my solution is more readable - choose whatever works for you.

Here is how I accomplished this problem (using MomentJS - and TypeScript):

First determine your boundaries (start date, end date):

const beginningOfDay = moment(dateToFetch, 'YYYY-MM-DD').startOf('day');
const endOfDay = moment(dateToFetch, 'YYYY-MM-DD').endOf('day');

Next, build your query (I happen to be using a ternary):

const args : any = mostRecent ? {  where: { user_uid }, order: [['log_timestamp', 'DESC']] } : { where: { user_uid, log_timestamp: {
            [Op.gte]: beginningOfDay,
            [Op.lte]: endOfDay,
        }},
    };

Finally - execute the query:

const userMessages: IUserMessages = await models.message.findAll(args);

Exemplar