[BUG]: invalid timestamp conversion when using PostgreSQL with TimeZone set to UTC

[VladBrok]

What version of drizzle-orm are you using?

0.28.6

What version of drizzle-kit are you using?

0.19.13

Describe the Bug

1. Create a table with timestamp column, mode="string". For example:

export const user = pgTable(
  "user",
  {
    id: uuid("id").primaryKey().notNull(),
    registeredAt: timestamp("registered_at", { mode: "string" }),
  }
);

2. Connect to the PostgreSQL database, after connection, execute SET TIME ZONE 'UTC';

const connectionString = process.env.DATABASE_URL || "";
const client = postgres(connectionString);
const drizzle = drizzle(client);
await drizzle.execute(sql`SET TIME ZONE 'UTC';`);

At this point, executing SHOW TIMEZONE shows 'UTC'
3. Insert a date to the timestamp column as ISO string. For example:

await drizzle.insert(user).values({ registered_at: '2023-11-30T18:30:04.823Z' }) // ISO

At this point, getting table data via pgAdmin returns a valid date: '2023-11-30T18:30:04.823Z' (in UTC, not converted to any timezone because SET TIME ZONE 'UTC' was executed). However, drizzle returns '2023-11-30T15:30:04.823Z' (-3 hours). My timezone is +3.

Expected behavior

when setting TimeZone to UTC in PostgreSQL, drizzle respects this setting and does not convert timestamp, but saves and retrieves it as-is (in UTC)

Environment & setup

database: PostgreSQL

[VladBrok]

I tried to create a custom type similar to this: #833, but it didn't worked.
The workaround is to use mode='date' instead of mode='string'. I would prefer to use string but OK.
The problem with this workaround is that i'm using introspection to generate drizzle schema from database, and it automatically sets timestamp to mode="string". To fix this, after running drizzle introspect command, I run the script that changes timestamp mode="string" to mode="date".

    const fileContent = await fs.readFile("./migrations/schema.ts", {
      encoding: "utf-8",
    });
    const fixedTimestamps = fileContent.replace(
      /(timestamp\(.+?, { mode: ')string(' }\))/g,
      "$1date$2"
    );
    await fs.writeFile("./migrations/schema.ts", fixedTimestamps);

[AndriiSherman]

Should be fixed in drizzle-orm@beta. I would appreciate some feedback to confirm whether this issue has been resolved in this tag.

I plan to release it in version 0.30.0 tomorrow or within the next few days; I simply aim to address this substantial set of issues we're encountering. I'll be duplicating this message across all similar issues we're facing.

[AndriiSherman]

Fixed in drizzle-orm@0.30.0

Please check release notes for more info