0

Actually I am using sqlite database(tables). Node.js for back-end. Now I need to get the records that fall under two specific dates.(for example 24.04.2020 11.00 AM to 28.04.2020 02.00 pm) How I do this? in what format I need to store the date and time in database? and how I compare?

Balaji G
  • 27
  • 8
  • you can use a package like sqlite3 and then use this [reference](https://stackoverflow.com/questions/19924236/query-comparing-dates-in-sql) to create the right query – Nayan Apr 30 '20 at 12:31
  • Time is also matter for me.. For Example 24.04.2020 11.00 AM to 28.04.2020 02.00 pm – Balaji G Apr 30 '20 at 13:13
  • Then you can use timestamp. Check the [reference](https://www.mysqltutorial.org/mysql-timestamp.aspx) also explore moment.js to manipulate time in js. – Nayan Apr 30 '20 at 13:17

2 Answers2

0

Sequelize uses js date. So you can do something like : 

const { Op } = require("sequelize");

Foo.findAll({
  where: {

    // myDateColumn < [timestamp] AND myDateColumn > [timestamp]
    {
      myDateColumn: {
        [Op.lt]: new Date(),
        [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
      }
    },


    // myDateColumn in range
    [Op.notBetween]: [new Date(), new Date(new Date() - 24 * 60 * 60 * 1000)]
  }
});

I also recommend using moment.js or luxon (personal favorite) to handle the dates.

Using luxon to format dates :

const DateTime = require('luxon');

// the reason we love luxon is because it's easy to set the timezone 
const startDate = DateTime.local().setZone('America/New_York').fromISO('2020-04-24T11:00:00').toJSDate();

Read more here : https://sequelize.org/master/manual/model-querying-basics.html

Segev -CJ- Shmueli
  • 1,535
  • 14
  • 15
0

const currentDate = new Date().toISOString().slice(0, 10); // get current date in ISO format try { const competitions = await CompetitionTable.findAll();

const competitionsWithActiveFlag = competitions.map(competition => {
  const isActive = (
    competition.start_date <= currentDate &&
    competition.end_date >= currentDate
  );

  const competitionData = competition.toJSON();
  console.log(competitionData, isActive);
 
});
shraddha tiwari
  • 1