Drizzle Team•2y ago

Issues with timestamp precision with postgres

Hi, I posted the other day about a bug with the date, but it seems this issue runs deeper. https://discord.com/channels/1043890932593987624/1043890932593987627/1136224592764080268 I am also using timestamp which has a similar issue, when using mode string drizzle is returning a Date instance (conflicting with the generated types) To make matters worse, when using mode: 'date' we lose all millisecond precision This is the column I am defining

createdAt: timestamp('created_at', { mode: 'string', precision: 3 }).defaultNow().notNull();

createdAt: timestamp('created_at', { mode: 'string', precision: 3 }).defaultNow().notNull();

This maintains the precision, but then the types are incorrect. Has anyone faced this issue? Is this is a bug in drizzle?

6 Replies

Ghostman•10mo ago

Hi @pandareaper Did you solve your problem?

pandareaperOP•10mo ago

Yeah I ended up creating a customType to address this. I saw a post yesterday suggesting it might have just been fixed though

Ghostman•10mo ago

Ghostman•10mo ago

I'm getting this error because my current data type is timestamp(3) but new type is timestamp (3) - there is space between timestamp and precision. Can you please share the migration data of timestamp so that I can refer it?

pandareaperOP•10mo ago

This is my custom timestamp data type

export const pgTimestamp = customType<{ data: Date; driverData: Date }>({
  dataType() {
    return 'timestamptz(3)';
  },
  toDriver: (date: Date) => {
    return date;
  },
  fromDriver: (date: Date) => {
    return date;
  },
});

export const pgTimestamp = customType<{ data: Date; driverData: Date }>({
  dataType() {
    return 'timestamptz(3)';
  },
  toDriver: (date: Date) => {
    return date;
  },
  fromDriver: (date: Date) => {
    return date;
  },
});

Looking at my migrations I don't see any space, I can't see how drizzle would introduce that either. Have you put that space there in your datatType() ?

Ghostman•10mo ago

export const requests = pgTable("Requests", {
...
    createdAt: timestamp("createdAt", { precision: 3, mode: 'date' }).defaultNow(),
    updatedAt: timestamp("updatedAt", { precision: 3, mode: 'date' }),
...
}

This is my schema.ts file

export const requests = pgTable("Requests", {
...
    createdAt: timestamp("createdAt", { precision: 3, mode: 'date' }).defaultNow(),
    updatedAt: timestamp("updatedAt", { precision: 3, mode: 'date' }),
...
}

This is my schema.ts file

And I didn't put the space to my type.

CREATE TABLE IF NOT EXISTS "Requests" (
    ...
    "createdAt" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" timestamp(3),
    ...

);

CREATE TABLE IF NOT EXISTS "Requests" (
    ...
    "createdAt" timestamp(3) DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" timestamp(3),
    ...

);

This is my sql file that was generated after introspecting the database. Could you please share these files?

Gaming

Programming

Issues with timestamp precision with postgres