Filters on dates / timestamps with SQLite

[oreoluwa-bs]

I'm using sqlite (Better-sqlite).
How do you perform filters on dates. Non of the below options work

Im trying to get rows that where created within a particular month of the year

  const tableRows = await db
  .select()
  .from(table)
  .where(      
      // sql.raw(`strftime('%Y', table.${table.createdAt.name}) = ${year}`)
      // sql`strftime('%m', ${table.createdAt}) = ${month}`
      // sql`strftime('%Y-%m', ${table.createdAt}) = ${year}-${month}`
      between(table.createdAt, new Date(`1-${month}-${year}`), new Date())
      // gte(table.createdAt, new Date(`1/${month}/${year}`).)
    )

And this is the schema

import { integer, text, sqliteTable } from "drizzle-orm/sqlite-core";
import { sql, type InferSelectModel } from "drizzle-orm";
import { nanoid } from "nanoid";
import { users } from "./users";

export const table = sqliteTable("table", {
  id: integer("id")
    .primaryKey().autoIncrement(),

  createdAt: integer("created_at", { mode: "timestamp_ms" })
    .notNull()
    .$defaultFn(() => sql`CURRENT_TIMESTAMP`),
});

[Angelelz]

You are using an integer to hold your datetime. You can do the following:

between(table.createdAt, (new Date(`1-${month}-${year})).valueOf(), new Date().valueOf())

[oreoluwa-bs]

Thanks.
This threw an error cause I believe drizzle expects a date and converts it on your behalf to an integer using valueof();

But I tried using between again and it worked pretty well