WHERE, Date, and BETWEEN

See original GitHub issue

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

Issue Analytics

State:
Created 6 years ago
Reactions:16
Comments:18 (5 by maintainers)

Top GitHub Comments

102reactions

pie6kcommented, Apr 16, 2019

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()),
      },
    });

33reactions

umutyerebakmazcommented, Apr 27, 2021

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.