英文:
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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论