Drizzle ORM date conversion problem between Javascript and PostgreSQL?

Question

Prerequisite: my timezone is GMT+3

I have a model drizzle model

export const events = pgTable('events', {
    id: serial('id').primaryKey(),
    name: varchar('name', {length: 256}).notNull(),
    date: date('date').notNull(),
});

I've inserted a record to the database

# insert into events (name, date) values ('Foobar', '2023-03-30');

When I query it it shows correctly in the result relation

 id |  name   |    date
----+---------+------------
100 | Foobar  | 2023-03-30

Now if I run the following code

const foo = await db.query.events.findFirst({
    where: eq(events.id, 100),
});
console.log(foo.date);

I get

2023-03-30T00:00:00.000Z

But if I view the database via drizzle-kit Studio, it shows the date as

2023-03-29T21:00:00.000Z

AND, if I use the foo's date value in another query

await db.query.events.findFirst({
    where: lt(events.date, foo.date),
    orderBy: desc(races.date),
});

I get the same record as I already have in the foo, not the one with earlier date - probably due to the fact that my filter value is 2023-03-30 but the database sees it as 2023-03-29. My analysis is that the lt (less than) comparison compares apple's and orange's. But I don't understand why? What should I do to fix it?

Do I need to set the database's timezone to UTC, and if then how? Or is there some other way to solve this?

Answer 1

The fix was to change the date column's definition to

date: date("date", {mode: "date"}).notNull()

i.e. add the mode as

"date"