How to search for a range of Date in PostgresSQL using TypeORM?

In TypeORM, when you need to search for data within date ranges in a PostgreSQL database, you typically use the QueryBuilder to build SQL queries, enabling you to filter records based on specific date intervals. Below, I'll demonstrate this with a concrete example. Assume we have an entity named Order with a date field representing the order date. The objective is to retrieve all orders created within a specified date range. First, ensure your Order entity class includes the date field, for example:

typescript
@Entity()
export class Order {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    date: Date;

    // Other fields and decorators...
}

Next, use TypeORM's QueryBuilder to construct a query that filters orders between the given start and end dates:

typescript
import { getRepository } from "typeorm";
import { Order } from "./entity/Order";

async function findOrdersBetweenDates(startDate: Date, endDate: Date): Promise<Order[]> {
    // Get the Order repository
    const orderRepository = getRepository(Order);

    // Build the query
    const query = orderRepository.createQueryBuilder("order")
        .where("order.date >= :startDate", { startDate })
        .andWhere("order.date <= :endDate", { endDate });

    // Execute the query
    const orders = await query.getMany();
    return orders;
}

In this example, :startDate and :endDate are parameters in a parameterized query, which helps prevent SQL injection attacks. We specify the date range conditions using the where and andWhere methods. The getMany() method executes the query and retrieves all matching records.

In practice, you can call the findOrdersBetweenDates function, passing the start and end dates as arguments:

typescript
const startDate = new Date("2023-01-01");
const endDate = new Date("2023-01-31");

findOrdersBetweenDates(startDate, endDate)
    .then(orders => {
        console.log("Found orders count: ", orders.length);
        // Further processing of the found orders...
    })
    .catch(error => {
        console.error("Error querying orders: ", error);
    });

This enables you to search for data based on date ranges in a PostgreSQL database using TypeORM. This approach is not only suitable for dates but also for other range queries.