I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

• javascript
• mysql
• typeorm

2 Answers 2

Your .andWhere('t0.createdAt = :today') only selects rows created at the instant of today parameter. You have set this parameter as moment(), which is not a date.

Since we can safely assume no rows will be created in the future, your simplest solution here is: .andWhere('t0.createdAt >= :today'), which selects rows created AFTER 00:00 today.

You can bine the addWhere and setParameters into one:

.andWhere('t0.createdAt >= :today', { today: moment().toDate() }) // note moment.toDate()

Alternatively use the MySql CURDATE() function to get the current date:

.andWhere('t0.createdAt >= CURDATE()')

When you test this, I remend that you turn on TypeOrm full logging so you can see the actual generated SQL and you will be able to quickly solve any problems. See TypeOrm logging.

For TypeORM + PSQL

Sort items by today's date

.andWhere('t0.createdAt >= CURRENT_DATE')