TypeOrm QueryBuilder dynamic auto delete function with crons job not working

Question

Token for email verification is created with User registration and needs to be deleted from database within 24 hours with crons job help. In a delete function using query builder, token gets deleted only if date value is manually provided in form of string: {delDate: "2021-02-08T17:59:48.485Z" }. Here, all tokens with date before or equal 2021-02-08 get deleted, and is working fine. But thats a static input, manually put in hard code! Since this must be a dynamic input, I set up variable 'delTime',which stores current date minus 24 hrs in it, but it seems .where condition will not take a variable as value, and will not delete, as: {delDate: deltime}. In fact, 'delDate' consoles exactly the info I need, but it will only work in form of string. There is a ton of content online teaching how to delete stuff with a static value in typeorm querybuilder, but so hard to find with dynamic values.... How else can I make this work in a dynamic way ?

async delete(req: Request, res: Response){

      try {
      const tokenRepository = getRepository(Token);
      
      var delTime = new Date();
      delTime.setDate( delTime.getDate() - 1 );
      console.log(delTime) //consoles 24 hors ago


            await tokenRepository
            .createQueryBuilder()
            .delete()
            .from(Token)
            .where("tokenDate <= :deleteTime", { deleteTime: delTime})//value dynamically stored in variable does not delete
            //.where("tokenDate <= :deleteTime", { deleteTime: "2021-02-08T18:01:10.489Z"})//static hard code value deletes
            //.where("tokenDate <= :delTime", { delTime})//Variable put this way will not work either... 
            .execute();
  

  
      } catch (error) {
          res.status(404).send("Tokens not found");
          return;
      }

      res.status(200).send('Tokens deleted successfuly');
  }

Answer 1

Your parameterized query looks correct.

Switch on TypeOrm Query Logging to see the generated SQL, maybe you will be able to see what's going wrong. Paste the generated SQL into the SQL query console to debug.

Alternatively you can write your delete query without parameters and let Sqlite calculate current date -1 day:

.where("tokenDate <= DateTime('Now', '-1 Day'");  // for Sqlite

Note 1: Sqlite DateTime('Now') uses UTC time, so your tokenDate should use UTC also.

Note 2: This syntax is specific to Sqlite, you can do the same in other databases but the syntax is different, e.g. Microsoft SQL Server:

.where("tokenDate <= DATEADD(day, -1, SYSUTCDATETIME());  // for MS SQL Server