Theo's Typesafe Cult•4w ago

Querying a table with a timestamp returns an incorrect time with drizzle

Hi there, i'm using drizzle with a postgresql database. In one of my tables i have a timestamp. When a row gets created, the value is stored correctly in UTC time. However when I query the same row, the returned date object has an incorrect time (likely adjusted for my timezone) however it should be in UTC. table:

export const sessions = pgTable("session", {
  id: text().notNull().primaryKey(),
  secretHash: text().notNull(),
  lastVerifiedAt: timestamp().notNull(),
  createdAt: timestamp().defaultNow().notNull(),

  userId: text().notNull().references(() => users.id)
})

export const sessions = pgTable("session", {
  id: text().notNull().primaryKey(),
  secretHash: text().notNull(),
  lastVerifiedAt: timestamp().notNull(),
  createdAt: timestamp().defaultNow().notNull(),

  userId: text().notNull().references(() => users.id)
})

timestamp in db attached Date returned from query attached Even though the timestamp is correctly being stored in UTC time in the database, when queried, the Date object is in "UTC" time (as indicated by the Z at the end of the string), so its been adjusted for no reason. How can I fix this?

15 Replies

Daniel•4w ago

timestamp() ignores the time zone in postgres. you're probably looking for timestampz() ah, in drizzle you have to pass {withTimezone: true}

SamOP•4w ago

Even if the timestamp is being stored correctly in UTC in the db?

Daniel•4w ago

if they're stored correctly in UTC, it might just be related to how you print them haha. Since I'm pretty sure drizzle just returns a JavaScript Date object

SamOP•4w ago

But that's the weird thing, in the first screenshot i'm literally printing the exact output from the drizzle query

Daniel•4w ago

SamOP•4w ago

even with toISOString() it's the same thing or as toUTCString() anyways, using {withTimezone: true} works but it's just not very well documented and/or is a bug

Daniel•4w ago

https://orm.drizzle.team/docs/column-types/pg if you need more info :)

Drizzle ORM - PostgreSQL column types

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Daniel•4w ago

specifically this section

Daniel•4w ago

I agree that it's weird tho, not sure why postgres wants to be different so badly

SamOP•4w ago

but it's doing the complete opposite here

SamOP•4w ago

its returning a Date object that has been adjusted i'd have to do more debugging to see if it's an issue with the drizzle postgres driver or an issue with running it with bun or something else but just want to get back to my project 😂

msvc•4w ago

tbh i just do this

  createdAt: timestamp("created_at").$defaultFn(() => /* @__PURE__ */ new Date()),

  createdAt: timestamp("created_at").$defaultFn(() => /* @__PURE__ */ new Date()),

what is your system's timezone?

SamOP•4w ago

GMT+1 but it was storing correctly in UTC in the db Anyways switching to timestamptz worked

msvc•4w ago

nice yeah deserialize issues i guess

Gaming

Programming

Querying a table with a timestamp returns an incorrect time with drizzle

Did you find this page helpful?