Question 114200 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Find date range in Prisma ORM

I’m trying to filter vacations by date range, but I don’t know how to do it. Please help.

Database: postgresql
Backend: nestjs + prisma orm (4.0.0)
Frontend: React

The data about vacations is stored in the database in this form:

id: Int
userId: Int
dateStart: DateTime
dateEnd: DateTime

In the frontend filter, I have the fields:

{
   dateStart: Date;
   dateEnd: Date;
}

I do not understand how to make a request to the database so that I would output the result of the contact of two ranges.

The following request works incorrectly:

where: {
   dateStart: { gte: dto.dateStart },
   dateEnd: { lte: dto.dateEnd }
}

If, for example, I am a user on vacation from January 25 to February 2, and I specify sorting from January 1 to January 31 in the filter, then the vacation will not appear. In this example, it will not work because of non-compliance with the dateEnd conditions.

The only thing I could guess was to turn the input dateStart and dateEnd into an array of dates, and then go through the loop inside the where query. It works, but slowly. For example, if you filter for a whole year, then the request to the database turns out to be too big fat, Please tell me.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This problem is solved using the OR operator.

    Example:

    OR: [
   { dateStart: { lte: dto?.dateEnd }, dateEnd: { gte: dto?.dateStart } },
   { dateStart: { gte: dto?.dateStart }, dateEnd: { lte: dto?.dateEnd } },
],

    It's work for me. Thank you for all!


  2. 0

    I would start with writing down in plain words, what you want to search for.

    To my understanding (but please correct me), you want to find all vacations that fall (partly) in the specified date range.

    (It certainly helps to differentiate dateStart/dateEnd of the vacation and the filterStart/filterEnd of the filter.)

    Then you translate that into an algorithm, e.g. by analyzing the different cases with respect to these dates. The algorithm should be:

    • dateStart is before (or equal) filterEnd AND
    • dateEnd is after (or equal) filterStart

    As a last step (and I would leave that to you), you have to translate this algorithm into a prisma query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search