Searching data older than a Date with typeORM

Question

I am executing a query to Postgre DB to fetch data older than a specific date.

Here's my function

async filesListToDelete(): Promise<any> {
  return await this.fileRepository.find({
    where: { last_modified: { $lt: '2018-11-15 10:41:30.746877' } },
  });
}

Here's how I defined my File entity:

export class File {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ nullable: false })
  idFonc: number;

  @Column({ nullable: false })
  version: number;

  @Column('varchar', { length: 100, nullable: false })
  filename: string;

  @Column({ nullable: true })
  last_modified: Date;

  @Column({ nullable: false })
  device: boolean;

  @ManyToOne(type => Type, { nullable: false })
  @JoinColumn({ referencedColumnName: 'id' })
  type: Type;

  @OneToMany(type => FileDevice, filedevice => filedevice.file)
  fileDevice: FileDevice[];
}

I get this error

QueryFailedError: invalid input syntax for type timestamp: "{"$lt":"2018-11-15 10:41:30.746877"}"

Answer 1

You can use LessThan, the doc

async filesListToDelete(): Promise<any> {
  return await this.fileRepository.find({
   where: { 
       last_modified:  LessThan('2018-11-15  10:41:30.746877') },
});}

Answer 2

Also you can do this using createQueryBuilder as below:

    public async filesListToDelete(): Promise<any> {
        let record = await this.fileRepository.createQueryBuilder('file')
            .where('file.last_modified > :start_at', { start_at: '2018-11-15  10:41:30.746877' })
            .getMany();

        return record
    }

Answer 3

Either of these will fetch OLDER data

with built-in TypeORM operator (docs)

async filesListToDelete(): Promise<any> {
  return await this.fileRepository.find({
    where: { last_modified:  LessThan('2018-11-15  10:41:30.746877') },
  });
}

with PostgreSQL operator (docs)

public async filesListToDelete(): Promise<any> {
    let record = await this.fileRepository.createQueryBuilder('file')
        .where('file.last_modified < :start_at', { start_at: '2018-11-15  10:41:30.746877' })
        .getMany();

    return record
}

Answer 4

This is how I fetched the data using typeORM and PostgreSQL:

Query: "payment.userId = :userId AND payment.pgTxnNo = :transactionNumber AND payment.createdDateTime >= :timestampFrom AND payment.createdDateTime <= :timestampTo"

Params:

{
  userId: "c2ba135e",
  transactionNumber: "1234",
  timestampFrom: "2023-11-15T10:30:35",
  timestampTo: "2023-11-15T10:30:35",
}

Here are the full codes:

async fetchDetails(userId, transactionNumber, timestampFrom, timestampTo): Promise<any> {
    try {
      const query = "payment.userId = :userId AND payment.pgTxnNo = :transactionNumber AND payment.createdDateTime >= :timestampFrom AND payment.createdDateTime <= :timestampTo";

      const  params  = {
               userId: "c2ba135e-7dfc-4f2b-91bf-124d76754965",
               transactionNumber: "1234",
               timestampFrom: "2023-11-15T10:30:35",
               timestampTo: "2023-11-15T10:30:35",
        }

      const [users, totalCount] = await this.paymentRepo
        .createQueryBuilder('payment')
        .select([
          'payment.createdBy',
          'payment.pgTxnAmt',
        ])
        .where(query)
        .setParameters(params)
        .getManyAndCount();
      return { users, totalCount };
    } catch (error) {
       console.log(error)
    }
  }