CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Sujeet
Sujeet

Reputation: 3810

greater than and less than a date gives no records in sequelize but works with heidisql

It's really weird, I can't debug the issue. I also don't know what's causing the issue.

I have a query like below:

const sequelize = require('sequelize')
const Op = sequelize.Op
const TODAY_START = new Date().setHours(0, 0, 0, 0)
const NOW = new Date()

    const  data = await AssignedJob.findAll({
        where: {
            created_on: {
                [Op.gt]: TODAY_START,
                [Op.lt]: NOW
            }
        }
    })

It generates a query like below.

SELECT `id`, `emp_id`, `zone_id`, `job_id`, `status`, `commission`, `rating`,
`created_by`, `updated_by`, `created_on`, `updated_on` 
FROM `assigned_jobs` AS `AssignedJob` 
WHERE (`AssignedJob`.`created_on` > '2020-03-24 00:00:00' AND `AssignedJob`.`created_on` < '2020-03-24 17:18:15');

But data is just an [] empty array.

I also tried using [Op.between]: [START_DATE, NOW], but still I didn't get any record.

I copied the same query to heidsql and ran it, I get the result there.

What's happening here? Can someone explain?

Data type of created_on and updated_on in sequelize is DATE, in the table it's TIMESTAMP

Upvotes: 3

Views: 7218

Answers (2)

Sujeet
Sujeet

Reputation: 3810

I don't know the reason why that's happening, I got a fix for it. I have to use momentjs to achieve that.

const moment = require('moment')
const now = moment()
const  todayAssignedJobs = await AssignedJob.findAll({
         where: {
            created_on: {
                 [Op.gt]: now.startOf('day').toString(),
                 [Op.lt]: now.endOf('day').toString()
              },
            status: 1
           }
     })

Same query is still being generated, but it gives result instead of giving an empty array.

SELECT `id`, `emp_id`, `zone_id`, `job_id`, `status`, `commission`, `rating`, `created_by`, `updated_by`, `created_on`, `updated_on` FROM 
`assigned_jobs` AS `AssignedJob`
WHERE 
(`AssignedJob`.`created_on` > '2020-03-24 00:00:00' AND `AssignedJob`.`created_on` <'2020-03-24 23:59:59') 
AND `AssignedJob`.`status` = 1;

If someone got any explanation please comment or feel free to edit this answer.

Upvotes: 3

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57105

Use moment.js to format the date in 'YYYY-MM-DD HH:mm:ss'

const sequelize = require('sequelize')
const moment = require('moment');
const Op = sequelize.Op

function getDate(withoutTime) {
    const date = new Date();
    if (withoutTime) date.setHours(0, 0, 0, 0);
    return moment(date).format('YYYY-MM-DD HH:mm:ss');
}

const TODAY_START = getDate(true); // '2020-03-24 00:00:00'
const NOW = getDate(); // '2020-03-24 17:47:41'

Problem const TODAY_START = new Date().setHours(0, 0, 0, 0) will result in Unix time i.e seconds after 1st Jan 1970

const date = new Date().setHours(0, 0, 0, 0)

console.log(date); // return seconds after 1970 

const date1 = new Date();
date1.setHours(0, 0, 0, 0);

console.log(date1); // return date

Upvotes: 2

Related Questions