using between for query Dates on api Repository Typeorm

Question

I am doing an endpoint that will receive an array of strings , from date and to date

like this:

{
  "cage": [
    "100000",
    "100100",
    "130109",
    "130106"
  ],
  "from": "2020-05-01T00:00:00Z",
  "to": "2021-12-29T23:32:33.464Z"
}

This is my query:

return await this.returnRepository.findAndCount({
                where: {
                    cod_locality: In(cage),
                    status_id: 1,
                    shipment_id: IsNull(),
                    asn_date: Between(from, to)
                    //asn_date: Raw(asnDate => `${asnDate} => :from AND ${asnDate} <= :to`, { from, to }),
                }
            })

Question

Is a good idea to filter the dates using Between? Dates always are hard for me so its a little confusing.

I was thinking that the Between will be comparing 2 strings but not Date Object so I dont know if this would work fine

The other option I seek was the comment one but I'm not sure how to use it properly.

What do you guy think? Between will work or I need to change it for another one?

ps. I just remember the thing about the timezone of the database -.-

thank you very much

Answer 1

From this.

Use a custom Between.

Install date-fns for JavaScript dates easy manipulation.

import { Between } from 'typeorm';
import { format } from 'date-fns';

// TypeORM Query Operators
export const BetweenDates = (from: Date | string, to: Date | string) =>
  Between(
    format(typeof from === 'string' ? new Date(from) : from, 'YYYY-MM-DD HH:MM:SS'),
    format(typeof to === 'string' ? new Date(to) : to, 'YYYY-MM-DD HH:MM:SS'),
  );

// Query
return await this.returnRepository
                  .findAndCount({
                    where: {
                      cod_locality: In(cage),
                      status_id: 1,
                      shipment_id: IsNull(),
                      asn_date: BetweenDates(from, to),
                    }
                  });