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

Question

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.

Solution 1

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-%'

Solution 2

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;

Solution 3

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()

Comments

[Nir Alfasi]

The question is not clear, on one hand you're saying: "I have to retrieve a users record from the database via his date of birth" which implies you have the DOB, but then you say you don't. What are you trying exactly to do? fetch all users that were born on a specific date? what if two users were born on the same date?

[Vijender Kumar]

@NirAlfasi We have DOB in database in datetime format but when we are querying it from front end, we are just sending the dd-mm-yy. So the query is not filtering the records out. Anyhow, I found the solution and added it as an answer down below.

[Nir Alfasi]

This cannot be the solution to the problem (the way it's stated) since, as I mentioned above, there may be multiple customers with the same DOB.

[Jason White]

This is exactly what I needed, Thanks! Also if anyone is looking to make the date param dynamic from a javascript Date object, you can use .where(`DATE_TRUNC('day', "birthdatetime") = :date`, {date: yourDateObject.toISOString().split('T')[0] })

[Bernardo Marques]

You should add an explanation along code snippet to make it clearer.