How to get all records by created date for today in Sequelize - NodeJS?

How to solve the issue of date comparison with Sequelize? One to do it is using a datetime comparison with Sequelize.

Problem statement

Sequelize cannot query data - for a particular date, especially today - by a column whose Type is datetime.

Problem description

This feature is part of a file upload process whereby the application invalidates the status of all the files the user has uploaded the same day. Each file is connected to a database record in a table file containing the column status.

So I need to get all the rows of the table file having their created_date, which is of datetime type, of day.

When translated in SQL, the query looks as follows:

SELECT * FROM table WHERE date(date_created) = date(currdate)

'currdate' means current date, which can also be written as CURRENTDATE. The date_created value is casted to date as it only requires the date value. However, this query is not possible with Sequelize.

Solutions

From my research on the Internet and countless of experimentation, I've worked out that Sequelize neither has any builtin solution for such issue nor an option of modifying a column when querying as illustrated below:

result = await db.model.findAll({
  where: {
	date(column): sequelize.fn('currdate')
  }
}) 

The date fonction or any other fonction cannot be applied to the column in this case. I can also create a function that solely extracts the date from the datetime value. Or the easiest can be to change the date_created column type to date. Nevertheless, the datetime type is more accurate for logging and auditing purposes.

All the solution I am trying returns either an empty result set or everything - even though - there is numerous records for the day.

What are the alternatives?

1. Using Sequelize comparator e.g. $lte
2. Use of sequelize.fn 'currdate'
3. Code with Raw query, which is not recommended and is my last resort
4. Using findAll, filter and map on the result set

What is my preferred solution?

I prefer dealing directly with SQL because of the inbuilt efficiency. If the language can do the action, there is no point in reinventing the wheel. However, like this situation, I implement using the fourth option.

Conclusion

When working on a challenging task, I first and foremost - try to truly understand what's the goal - by writing down the problem and brainstorm several solutions. I prefer designing and following a strategy instead of brute forcing any solution.

Once done, I experiment with trial and error until I get a good enough solution.

If my articles help you and you want to support my work, you can buy me a coffee: