无法在typeorm中使用时间戳类型的createdAt列查询记录。

huangapple go评论65阅读模式
英文:

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

问题

`
抽象导出类 GeneralEntity 继承自 BaseEntity {

@PrimaryGeneratedColumn()
    id: number;

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

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

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

}`

这是我正在使用的实体。当我尝试从此实体检索具有特定创建日期的数据时,我提供的日期与实际不匹配,即使它们是相同的。

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

这总是一个空数组。

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

它不应该是一个空数组。

任何帮助都赞赏!

英文:

`
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!

答案1

得分: 1

在你的查询中,你正在将booking.createdAt与格式化的日期字符串进行比较。这可能会返回不正确的值,因为它可能会比较两个看起来像这样的日期字符串:

  • 2023-06-18 12:00:00(你的输入日期)
  • 2023-06-18 12:00:00.123(数据库中存储的日期)

这里有一个可能的解决方案,如果你想进行到秒的比较(忽略毫秒),可以使用DATE(booking.createdAt):

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

仅比较日期部分(将时间戳转换为日期):

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();
英文:

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

huangapple
  • 本文由 发表于 2023年6月19日 12:42:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/76503656.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定