Ca not query record with createdAt column with timestamp type in typeorm

Question

` export abstract class GeneralEntity extends BaseEntity {

@PrimaryGeneratedColumn()
    id: number;

@CreateDateColumn({ name: 'createdAt' })
    createdAt: Date;

@UpdateDateColumn({ name: 'updatedAt' })
    updatedAt: Date;

@DeleteDateColumn({ name: 'deletedAt' })
    deletedAt: Date;

}`

This is an entity I am using. When I try to retrieve data from this entity with specific created date, date I gave does not match even it was the same.

const bookedRoomEntities = await this.createQueryBuilder('booking')                 
    .where(`booking.roomId = :roomId`, { roomId })                 
    .andWhere(`booking.createdAt = :date`, { date: format(date, 'yyyy-MM-dd HH:mm:ss') })                 
    .orderBy('booking.start')                 
    .getMany();

bookedRoomEntities

this is always an empty array.

const bookedRoomEntities = await this.createQueryBuilder('booking')                        
    .where(`booking.roomId = :roomId`, { roomId })                  
    .andWhere(`booking.createdAt = :date`, { date: format(date, 'yyyy-MM-dd HH:mm:ss') })                  
    .orderBy('booking.start') 
    .getMany();

It should not be an empty array.

Any help is appreciated!

Answer 1

In your query, you are comparing booking.createdAt with a formatted date string. This might return incorrect values, as it could be comparing two date strings that look like this:

• 2023-06-18 12:00:00 (your input date)
• 2023-06-18 12:00:00.123 (the date stored in the database)

Here's a possible solution, where DATE(booking.createdAt) is used if you want to compare down to the second (ignoring milliseconds):

const bookedRoomEntities = await this.createQueryBuilder('booking')
    .where(`booking.roomId = :roomId`, { roomId })
    .andWhere(`date_trunc('second', booking.createdAt) = :date`, { date: format(date, 'yyyy-MM-dd HH:mm:ss') }) 
    .orderBy('booking.start')
    .getMany();

Compare only the date parts (casts the timestamp to a date):

const bookedRoomEntities = await this.createQueryBuilder('booking')
    .where(`booking.roomId = :roomId`, { roomId })
    .andWhere(`booking.createdAt::DATE = :date`, { date: format(date, 'yyyy-MM-dd') }) 
    .orderBy('booking.start')
    .getMany();