Typeorm. use only the date part while querying via date and exclude the timestamp part

user_2122297 0
Viewed 13637

I have a use case where I have to retrieve a users record from the database via his date of birth. The thing is that we have stored the date of birth of the user as datetime object which makes it very difficult to retrieve the users data, as we can not provide the exact date with timestamp.

I tried looking for a functions support in typeorm using which I can just compare the provided date with only the date part of the birth date in database but did not find any reference anywhere.

typeorm nestjs node.js orm
5 Answers

Don't convert your column to a string. Here's how to do it with a date/datetime/timestamp.

import { startOfDay, endOfDay } from 'date-fns';
import { Between, Equal } from "typeorm";
//...
let findArgs = { 
  where:{
    date: Between(startOfDay(webInputArgs.date).toISOString(), endOfDay(webInputArgs.date).toISOString()), 
    userId: Equal(ctx.req.session.userId)
  }
};
return entity.find(findArgs) as any;
user_41807970

Found the solution, We can actually use the LIKE operator, the only prerequisite is that we would have to convert the date to string,

something like

date_of_birth::text LIKE '2011-01-%'
user_21222970

You can use the query builder and the postgresql DATE_TRUNC() function:

const usersBornToday = await getManager()
    .createQueryBuilder(User, "user")
    .where(`DATE_TRUNC('day', "birthdatetime") = :date`, {date: '2021-03-27'})
    .getMany()
user_14056290

If the DB type is MySQL, the CONVERT function will achieve the same approach used by @Vijender, which is to convert timestamp to text as below:

let date = '2022-01-25'; //yyyy-mm-dd, for Regex validation - /\d{4}\-\d{2}\-\d{2}/
date = `${date}%`;
queryResult.where('CONVERT(date_of_birth, char) LIKE :date', { date });
user_108219810 
Todo:only show today stock list
      const start = moment().startOf('day').toDate();
      const end = moment().endOf('day').toDate();
     
      const query=getManger()
        .createQueryBuilder(stocks, 'stocks')
        .andWhere('stocks.createdAt BETWEEN :start AND :end', { start, end })
user_169209900
Related