Drizzle Team•12mo ago

How to select part of a string in Drizzle?

I have dates in ISO format (Stored as Strings) in my SQLite database. I need to be able to find a match only querying the year-month-day. Attached are two different ways I have tried this and neither seem to work.

const timers = await db.select().from(timerSchema).where(sql`to_tsvector ('simple', '${timerSchema date}') @@ to_tsquery('simple', '${queryDayString}'))`;

const timers = await db.select().from(timerSchema).where(sql`to_tsvector ('simple', '${timerSchema date}') @@ to_tsquery('simple', '${queryDayString}'))`;

const timers = await db.select().from(timerSchema).where(sql`timer_date like '%${queryDayString}%'`);

const timers = await db.select().from(timerSchema).where(sql`timer_date like '%${queryDayString}%'`);

2 Replies

JavaDadOP•12mo ago

Update: Seems like this is the solution.

const timers = await db
      .select()
      .from(timerSchema)
      .where(sql`timer_date like ${`%${queryDayString}%`}`);

const timers = await db
      .select()
      .from(timerSchema)
      .where(sql`timer_date like ${`%${queryDayString}%`}`);

Mykhailo•12mo ago

Hello @JavaDad! I think you can use DATE_TRUNC function:

 const date = '2023-12-22';
 const result = await db
   .select()
   .from(users)
   .where(sql`DATE_TRUNC('day', "myDate") = ${date}`);

 const date = '2023-12-22';
 const result = await db
   .select()
   .from(users)
   .where(sql`DATE_TRUNC('day', "myDate") = ${date}`);

oh, sorry, my bad, I thought about PostgreSQL and didn't notice that you need for SQLite 😅

Gaming

Programming

How to select part of a string in Drizzle?