Kysely•8mo ago

How to cast to date. eg : `DATE(created_at)`

How to write this query in kysely

SELECT DATE(created_at) AS date, SUM(points) AS score 
FROM table 
WHERE id = '...' 
GROUP BY date 
ORDER BY date;

SELECT DATE(created_at) AS date, SUM(points) AS score 
FROM table 
WHERE id = '...' 
GROUP BY date 
ORDER BY date;

My attempt

const result = await db
  .selectFrom('table')
  .select(({ fn }) => [
    fn.sum<number>('table.points').as('score')
  ])
  .where('id', '=', id)
  // .groupBy('created_at')
  // .orderBy('created_at')
  .execute()

const result = await db
  .selectFrom('table')
  .select(({ fn }) => [
    fn.sum<number>('table.points').as('score')
  ])
  .where('id', '=', id)
  // .groupBy('created_at')
  // .orderBy('created_at')
  .execute()

Solution:

Hey 👋 Does this work for you? ```ts...

Jump to solution

2 Replies

Solution

Igal•8mo ago

Hey 👋 Does this work for you?

const rows = await db
  .selectFrom("table")
  .where("id", "=", "123")
  .select((eb) => [
    eb.cast<Date | string>("created_at", "date").as("date"),
    eb.fn.sum("points").as("score"),
  ])
  .groupBy("date")
  .orderBy("date")
  .execute();

const rows = await db
  .selectFrom("table")
  .where("id", "=", "123")
  .select((eb) => [
    eb.cast<Date | string>("created_at", "date").as("date"),
    eb.fn.sum("points").as("score"),
  ])
  .groupBy("date")
  .orderBy("date")
  .execute();

https://kyse.link/TcWWP

dev﹫localhostOP•8mo ago

@Igal Yes, thank you!

Gaming

Programming

How to cast to date. eg : `DATE(created_at)`