Query result count from drizzle is different than the query result through dbms (e.g. DBeaver)

[deanrih]

I am not sure if this is a bug within drizzle-orm or not so i decide to post it here in the discussion first.

So, basically I noticed that somehow given the same query (through query builder/fluent API/raw query) and executed on both within drizzle and using database management system software like DBeaver, it somehow results in a different amount of rows.

for example consider this query

select
	"table_a"."id"
from
	"table_a"
left join "table_b" on "table_b"."table_a_id" = "table_a"."id"
where
	date_trunc('day', "table_b"."timestamptz_field") = '2026-01-01'::date

The table and column name is intentionally replaced due to sensitive information

when being executed in DBeaver, it returns 2 rows of id based on our dataset and it is the correct one, however, when executed by drizzle like

const query = sql`
    select
        "table_a"."id"
    from
        "table_a"
    left join "table_b" on "table_b"."table_a_id" = "table_a"."id"
    where
        date_trunc('day', "table_b"."timestamptz_field") = '2026-01-01'::date
`;
const result = await db.execute(query);
// or
const query = db
    .select({ id: schema.tableA.id })
    .from(schema.tableA)
    .leftJoin(schema.tableB, eq(schema.tableB.tableAId, schema.tableA.id))
    .where(eq(sql`date_trunc('day', ${schema.tableB.timestamptzField})`, sql`'2026-01-01'::date`));
const result = await query;

all the variable are the same, using the same connection string, same user, same database, same schema, and i couldn't figure out what is the cause of this, and i have to reiterate, the same environment, the same exact raw query (when using db.execute), yet it returns different amount of result

so if any from drizzle team happen to read this, is there a way to figure out or to see some hidden variable that might indicate a different environment? underlying transformation/processing? or just something to steer me towards the direction that would help me closing in the cause of the problem

i've also tried to compare the output of this query, and it is exactly the same between both DBeaver output or drizzle

select current_user, current_setting('transaction_isolation'), current_database(), current_schema(), inet_server_addr(), inet_server_port();

there's no example project to reproduce yet, and i haven't tried to play around with the where clause since the provided one is the easiest to compare based on our own datasets

drizzle-orm: ^0.45.1 # will try updating to ^0.45.2 later
drizzle-kit: ^0.31.9

PostgreSQL: 18.3

edit:

for clarification, my other query seems to be fine and returns the same results when being compared with the dbms result (query extracted through query.toSQL().sql), however this particular query stunned me

[MILLERMARRU]

date_trunc('day', timestamptz_field) is timezone-sensitive, Postgres converts the timestamptz to the session's TimeZone setting before truncating to a day boundary, so the same underlying instant can land in a different calendar day depending on which timezone the session is in. That matches your report exactly: the query that differs is the one with date_trunc on a timestamptz, while your other queries (presumably not timezone-sensitive) return matching results.

Neither drizzle's postgres.js/node-postgres client nor DBeaver's JDBC driver necessarily negotiate the same session timezone by default, DBeaver commonly defaults to your client machine's local timezone unless configured otherwise, while Node Postgres drivers typically don't set TimeZone explicitly and just inherit whatever the server's default is. If those two differ, your = '2026-01-01'::date boundary shifts between the two connections.

To confirm this is it, compare the two sessions directly:

SHOW timezone;

Run that once through drizzle (db.execute(sqlSHOW timezone)) and once in a DBeaver tab against the same database. If they differ, that's your answer. With postgres.js you can pin it explicitly:

const sql = postgres(connectionString, {
  connection: { TimeZone: 'UTC' },
})

(node-postgres: set PGTZ=UTC in the environment, or run SET TIME ZONE 'UTC' right after connecting). Pin both sides to the same explicit zone, UTC is usually the least surprising choice, rather than leaving it to whatever each client's default happens to be, and the two row counts should converge.