Drizzle ORM & PostgreSQL - get rows from last N days

30 September 2023 · Tech · No AI

Let’s say you have a table with timestamp column.

import { pgTable, product, serial, timestamp } from "drizzle-orm/pg-core";

export const purchases = pgTable("purchases", {
  id: serial("id").primaryKey(),
  created_at: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  product: text("product").notNull(),
});

You can get rows from last N days using sql operator and PostgreSQL INTERVAL operator.

await db
  .select()
  .from(profits)
  .where(gt(profits.created_at, sql`NOW() - INTERVAL '7 days'`));

Drizzle will generate SQL like this:

select "id", "created_at", "product" from "purchases" where "purchases"."created_at" > NOW() - INTERVAL '7 days'

Related posts