WHERE, Date, and BETWEEN

[voodooattack]

It appears that there is a bug with how QueryBuilder is handling dates. I only tested with SQLite, but it is possible that this is happening with other implementations.

  const start = new Date(date);
  start.setHours(0, 0, 0, 0);
  const end = new Date(start);
  end.setDate(start.getDate() + 1);
  const result = await model.createQueryBuilder('q')
    // This doesn't work:
    // .where('q.createdAt BETWEEN :start AND :end', { start, end })
    // This does:
    .where(`q.createdAt BETWEEN '${start.toISOString()}' AND '${end.toISOString()}'`)
    .getCount();
  console.log(result);
  return result;

Query in first case:

SELECT COUNT(DISTINCT("q"."id")) as "cnt" FROM "page" "q" WHERE "q"."createdAt" BETWEEN $1 AND $2 
[ 2017-11-16T22:00:00.000Z, 2017-11-17T22:00:00.000Z ]

Query in second case:

SELECT COUNT(DISTINCT("q"."id")) as "cnt" FROM "group" "q" WHERE "q"."createdAt" BETWEEN '2017-11-16T22:00:00.000Z' AND '2017-11-17T22:00:00.000Z'

Versions tested: 0.1.5, 0.1.6

[pleerock]

@voodooattack are you still interested in this issue? If yes, then please check it on @next - lot of date-related things were fixed in there. And please open a new issue if you have problems in there. Thanks.

[eddieajau]

@pleerock confirming that issue is still present in typeorm@0.3.0-alpha.14.

public async countUnique(after?: Date, before?: Date): Promise<number> {
    const filters: ArticleAnalyticsFilter = {
      before: before.toISOString() as any,
      after: after.toISOString() as any,
      // before,
      // after
    };

    const result = await this.getRepository()
      .createQueryBuilder()
      .select('COUNT(DISTINCT(objectId))', 'unique')
      .andWhere('createdAt >= :after')
      .andWhere('createdAt < :before')
      .setParameters(filters)
      .getRawOne();

    return result.unique;
  }

The logging is not useful. Log from using raw after and `before:

#  Log from using raw `after` and `before
query: SELECT COUNT(DISTINCT(objectId)) AS "unique" FROM "activity_article" "ArticleAnalytics" WHERE createdAt >= ? AND createdAt < ? --PARAMETERS : ["2018-01-02T00:00:00.000Z", "2018-01-05T00:00:00.000Z"]
# Log using `toISOString`
query: SELECT COUNT(DISTINCT(objectId)) AS "unique" FROM "activity_article" "ArticleAnalytics" WHERE createdAt >= ? AND createdAt < ? -- PARAMETERS: ["2018-01-02T00:00:00.000Z","2018-01-05T00:00:00.000Z"]

If you can point me to the test that would check this, I can have a go at making a failing test.

Thanks in advance.

[sushilbansal]

I am facing the same issue. Without toISOString()
createdAt > createdAt = true

[pleerock]

@eddieajau I don't think SQL type used by createdAt uses ISO date format. Simply try to execute following SQL query:

SELECT COUNT(DISTINCT(objectId)) AS "unique" FROM "activity_article" "ArticleAnalytics" WHERE createdAt >= '2018-01-02T00:00:00.000Z' AND createdAt < '2018-01-05T00:00:00.000Z'

And see if it doesn't work. If it doesn't work then you need to specify a proper date format.

[sushilbansal]

Hi @pleerock i think i found one reason:
createdAt through quering: 2018-12-17 20:52:56.357 +00:00
but DB createdAt is : 2018-12-17 20:52:56.357351+00

When i use sql query and use the DB format it works.

Do you know how i can get the full format time stamp with timezone into Java script. I Tried various ways to get 6 milliseconds digits but at most i get only 3 (as mentioned above).

[Kononnable]

If you need high-resolution time you can use process.hrtime()

[pie6k]

import { Between } from 'typeorm';
import { addYears, subYears } from 'date-fns';

// TypeORM Query Operators
export const AfterDate = (date: Date) => Between(date, addYears(date, 100));
export const BeforeDate = (date: Date) => Between(subYears(date, 100), date);

// leter
    return Event.find({
      where: {
        date: AfterDate(new Date()),
      },
    });

[esunea]

Hello !
Thanks for the infos.
got the same issue, my solution was about missing quotes around the date.

 getSqlFormatDate (date:Date){
    return "'"+date.toISOString()+"'";
  }

.where("temps.date BETWEEN '" +this.getSqlFormatDate(date_d)+"' AND '" + this.getSqlFormatDate(date_f)+"'")

but i don't like what i've done. i don't feel confident about sql injections
i'd like to try pie6K method but i don't understand where to put the return statement or what it related to.

[emirom]

In JavaScript, you can easily make anISO8606 Date in this way:

const date = new Date(2020,2,2)
const iso  = date.toISOString()

Both Typescript And PostgreSQL know ISO8606 well and

So this works like a chram:

if (orderInput.begining && orderInput.ending)
    query.andWhere(
       `"order"."createdAt"
    BETWEEN :begin
       AND :end`
    ,{ begin: orderInput.begining, end: orderInput.ending);

[SamuelMarks]

*8601

ISO8606 is https://www.iso.org/standard/15911.html - Plastics — Prepregs — Bulk moulding compound (BMC) and dough moulding compound (DMC) — Basis for a specification

[phil294]

SELECT COUNT(DISTINCT("q"."id")) as "cnt" FROM "group" "q" WHERE "q"."createdAt" BETWEEN '2017-11-16T22:00:00.000Z' AND '2017-11-17T22:00:00.000Z'

The problem described in this issue is a bug explained in #2286, and not yet fixed. For now, you need to format your dates in the where clause as YYYY-MM-DD HH:MM:SS manually

[umutyerebakmaz]

if (filter.startDate && filter.endDate) {
    query.andWhere('"createdAt" BETWEEN :startDate AND :endDate', { startDate: filter.startDate, endDate: filter.endDate });
}

I can add a conditional sentence to the query builder and I use it like this, maybe it will be a useful example for others.

[laratutoteam]

Date Between Query is: https://laratuto.com/date-between-query-in-mysql/

[kushwaha1]

select * from voucher where voucher date between date1 and date2 and subpartyid = subpartyid
how to write this query in typeorm?

[JoseParraRq]

It appears that there is a bug with how QueryBuilder is handling dates. I only tested with SQLite, but it is possible that this is happening with other implementations.

  const start = new Date(date);
  start.setHours(0, 0, 0, 0);
  const end = new Date(start);
  end.setDate(start.getDate() + 1);
  const result = await model.createQueryBuilder('q')
    // This doesn't work:
    // .where('q.createdAt BETWEEN :start AND :end', { start, end })
    // This does:
    .where(`q.createdAt BETWEEN '${start.toISOString()}' AND '${end.toISOString()}'`)
    .getCount();
  console.log(result);
  return result;

Query in first case:

SELECT COUNT(DISTINCT("q"."id")) as "cnt" FROM "page" "q" WHERE "q"."createdAt" BETWEEN $1 AND $2 
[ 2017-11-16T22:00:00.000Z, 2017-11-17T22:00:00.000Z ]

Query in second case:

SELECT COUNT(DISTINCT("q"."id")) as "cnt" FROM "group" "q" WHERE "q"."createdAt" BETWEEN '2017-11-16T22:00:00.000Z' AND '2017-11-17T22:00:00.000Z'

Versions tested: 0.1.5, 0.1.6

muchas gracias esa ultima parte del between fue excelsa me ayudo muchisimo en mi trabajo

[vladotastc]

Hello there, I'm attempting to compare only time values. example: if 23:57 is BETWEEN 23:55 and 00:00. Any help

[crisvilla-oss]

Hello there, I'm attempting to compare only time values. example: if 23:57 is BETWEEN 23:55 and 00:00. Any help

I think you should try comparing numbers instead of dates, the equivalent of 23:57 in milliseconds is between 23:55 and 24:00. Compare the equivalent of hours in milliseconds.

[luojiong]

I used "Between" to query in MongoDB is invalid, and then I continued to try "LessThan", "Not". The filtered data is always empty, but I used tools to query my data. I verified many times. Do you have any suggestions

this.robotRepo.find({
      order: {
        value: 'ASC',
      },
      where: {
        value: Between(5,10),
      },
    
    });