Convert date to timestamptz
Hello. I'm trying to convert the following query from TypeORM querybuilder to Kysely:
itemRepository
.createQueryBuilder()
.select()
.where({ id })
.andWhere('sys_period @> :timestamp::timestamptz', { timestamp })
.getOne();
My attempt:
db.selectFrom('item')
.selectAll()
.where('id', '=', id)
.where('sys_period', '@>', [timestamp]);
However type is mismatched, I need to somehow convert the Date to a timestamptz. Any idea where I could define such mapping?
3 Replies
In case of need you can always rely on kysely
sql to create raw sql. I mean that you can always cast values to something using raw sql like:
However, in your case, it really depends how you're mapping table types. Here sys_period I imagine is a column of type timestamptz. How have you mapped it as type in typescript? It depends on the driver you're using, but it probabily would end up being a Date itself, so you can type it that way and then you get the comparison working againColumn sys_period is defined that way (it's a tstzrange, I want to use the @> to check if it contains the date):
export const sysPeriodSettings: ColumnOptions & PrimaryColumnOptions = {
name: 'sys_period',
type: 'tstzrange',
nullable: false,
default: () => 'tstzrange(now(), null)',
};
// In entity:
@PrimaryColumn(sysPeriodSettings)
sysPeriod: string; // A string because TypeORM does not support timestamp ranges
How mapping is done is a good question as at the moment it's magically handled by TypeORM.Ok, but how's going to be handled with kysely and the driver you're going to use? Cause if it's going to be a string aswell, I don't know what the kysely
@> operator is going to enforce the right side to be. Whatever it's going to want as right side, you can always provide that with raw sql as I showed to you earlier
and you could create yourself a little helper function to help you go from Date to RawBuilder<WhateverYouNeed> by using raw sql template underneath