Kysely•7mo ago

Using sql.lit

Hey I have a question about using sql.lit and using dates

const query = kyselyBase
        .selectFrom("person")
        .select([
          sql.lit("(N/A)").as("asd"),
          sql.lit(true).as("asd2"),
          sql.lit(new Date()).as("zxc"),
          sql.lit(null).as("qwe")
        ]);

const query = kyselyBase
        .selectFrom("person")
        .select([
          sql.lit("(N/A)").as("asd"),
          sql.lit(true).as("asd2"),
          sql.lit(new Date()).as("zxc"),
          sql.lit(null).as("qwe")
        ]);

I get the date as string, is there a way to get the date as date ? is there a better way of doing it ?

6 Replies

Unknown User•7mo ago

Message Not Public

SneakOnYou•7mo ago

how do I use sql.lit with date ? is it possible ? because when I used it in the example above I got string instead of date

Unknown User•7mo ago

Message Not Public

koskimas•7mo ago

sql.lit means "interpolate this value to the SQL string as-is and don't use parameters". A Date gets turned into an ISO string and the db engine has no idea the string represents a Date. You need to cast it to tell the db the type.

SneakOnYou•7mo ago

Ok thank you

koskimas•7mo ago

So something like

const query = kyselyBase
  .selectFrom("person")
  .select([
    sql.lit("(N/A)").as("asd"),
    sql.lit(true).as("asd2"),
    sql`CAST(${new Date().toISOString()} AS timestamptz)`.as("zxc"),
    sql.lit(null).as("qwe")
  ]);

const query = kyselyBase
  .selectFrom("person")
  .select([
    sql.lit("(N/A)").as("asd"),
    sql.lit(true).as("asd2"),
    sql`CAST(${new Date().toISOString()} AS timestamptz)`.as("zxc"),
    sql.lit(null).as("qwe")
  ]);

if you're using postgres. The correct type is probably datetime on MySQL. Postgres also has a nicer cast syntax:

const query = kyselyBase
  .selectFrom("person")
  .select([
    sql.lit("(N/A)").as("asd"),
    sql.lit(true).as("asd2"),
    sql`${new Date().toISOString()}::timestamptz`.as("zxc"),
    sql.lit(null).as("qwe")
  ]);

const query = kyselyBase
  .selectFrom("person")
  .select([
    sql.lit("(N/A)").as("asd"),
    sql.lit(true).as("asd2"),
    sql`${new Date().toISOString()}::timestamptz`.as("zxc"),
    sql.lit(null).as("qwe")
  ]);

Gaming

Programming

Using sql.lit