date column not being returned as string by drizzle

Hello, I have the following table:

export const productSnapshot = pgTable("product_snapshot", {
    productId: text("product_id").notNull().references(() => product.id),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    version: bigint("version", { mode: "number" }).notNull(),
    material: text("material").notNull(),
    shape: text("shape").notNull(),
    alloy: text("alloy").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    thickness: bigint("thickness", { mode: "number" }).notNull(),
    dimension: text("dimension").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    weightByFoot: bigint("weight_by_foot", { mode: "number" }).notNull(),
    description: text("description").notNull(),
    effectiveDate: timestamp("effective_date", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
},

export const productSnapshot = pgTable("product_snapshot", {
    productId: text("product_id").notNull().references(() => product.id),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    version: bigint("version", { mode: "number" }).notNull(),
    material: text("material").notNull(),
    shape: text("shape").notNull(),
    alloy: text("alloy").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    thickness: bigint("thickness", { mode: "number" }).notNull(),
    dimension: text("dimension").notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    weightByFoot: bigint("weight_by_foot", { mode: "number" }).notNull(),
    description: text("description").notNull(),
    effectiveDate: timestamp("effective_date", { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
},

Whenever I try to query the effectiveDate from the drizzle table, TypeScript says the return type will be string, however, I verified that I am actually receiving a Date object. I am using mode 'string' as you can see. Is this a known issue?

6 Replies

pandareaper•15mo ago

timestamp has a few bugs, I don't use it but instead use a custom type with a timestamp(3) column. Be careful with timestamp as well, because it has a bug where it drops millisecond precision

DYELbrah•15mo ago

Darn, that sucks. We are actually using Drizzle Kit introspect and doing migrations with Supabase's CLI. So we can't really manually edit the effective date :/ I wonder if I can change the migration file somehow to avoid drizzle kit from using timestamp

Andrii Sherman•15mo ago

Sorry for a bit late response, just got time to answer all the questions in Discord I believe the milliseconds precision was fixed for MySQL. Will check Postgres as well Wasn't aware of timestamps string/date bug. Thanks for bringing that up(Will go through all GH issues after my DIscord help channel journey)

arxpoetica•12mo ago

Hey, still true? Right now I'm using

date: date('date', { mode: 'string' }),

date: date('date', { mode: 'string' }),

And the queried results are returned as date: 2023-11-10T00:00:00.000Z

arxpoetica•12mo ago

This might be the same issue: https://github.com/drizzle-team/drizzle-orm/issues/1407

GitHub

[BUG]: Wrong data type for postgres date colum · Issue #1407 · driz...

What version of drizzle-orm are you using? 0.27.2 What version of drizzle-kit are you using? 0.19.9 Describe the Bug Hey, First of all, we'd like to express our enthusiasm for using Drizzle - i...

arxpoetica•12mo ago

I ended up doing:

date: sql`to_char(${transactions.date}, 'YYYY-MM-DD')`,

date: sql`to_char(${transactions.date}, 'YYYY-MM-DD')`,

Gaming

Programming

date column not being returned as string by drizzle