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?
-
you can use a package like sqlite3 and then use this reference to create the right queryNayan– Nayan2020-04-30 12:31:50 +00:00Commented Apr 30, 2020 at 12:31
-
Time is also matter for me.. For Example 24.04.2020 11.00 AM to 28.04.2020 02.00 pmBalaji G– Balaji G2020-04-30 13:13:44 +00:00Commented Apr 30, 2020 at 13:13
-
Then you can use timestamp. Check the reference also explore moment.js to manipulate time in js.Nayan– Nayan2020-04-30 13:17:47 +00:00Commented Apr 30, 2020 at 13:17
Add a comment
|
2 Answers
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
Comments
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);
});
Comments
