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