mysql - How to get dates/times that fall between two columns including start and end dates using Sequelize

Question

I am trying to get all the values that fall between two dates using Sequelize in MySql database. However, I'm not getting the value that includes the end date, unless I use the day after as an end date. How can I include the end date in my results?

Example: If I search between January 4, 2021 07:00 am and January 5, 2021 05:00 pm I am only getting the values for January 4th, but If I search between January 4, 2021 07:00 am and January 6, 2021 05:00 pm I can see both, January 4th and January 5th. I am using the Op.lte and Op.gte operator but have no clue about what I'm missing here.

Here is my Express route:

const express = require("express");
const router = express.Router();
const PunchClock = require("../models").PunchClock;
const Employee = require("../models").Employee;
const { Op } = require("sequelize");

router.get("/all_dates/:id/:dateStart/:dateEnd", (req, res) => {
  Employee.findOne({
    where: {
      id: req.params.id,
    },
    include: {
      model: PunchClock,
      where: {
        punchInTime: { [Op.gte]: req.params.dateStart },
        punchOutTime: { [Op.lte]: req.params.dateEnd },
      },
    },
  })
    .then((employee) => {
      res.json(employee.PunchClocks);
    })
    .catch((err) => res.json(err));
});

module.exports = router;

Thanks in advance.

question from:https://stackoverflow.com/questions/65645959/how-to-get-dates-times-that-fall-between-two-columns-including-start-and-end-dat

Answer 1

I found the error in the front end. I was only sending the Date but not the time. Once I sent the Date and Time to the back-end, the values returned properly including the end date and time values.