[BUG]: Timestamp formatted differently if fetched as relation rather than directly

[jgudka]

What version of drizzle-orm are you using?

0.30.7

What version of drizzle-kit are you using?

No response

Describe the Bug

Full thread in Discord - https://discord.com/channels/1043890932593987624/1225414684119662746.

I'm seeing a little issue that I can't get to the bottom of, whereby timestamps are being formatted differently on retrieval from the DB, depending on whether the entity is being queried directly, or whether it is included via a relation on another entity when that is queried.

The entity in question here is a "Farm", which also has a 1 to many relationship with an "Assessment". We can either fetch this from the "farm" table as a standalone object (in which the timestamp appears to be formatted incorrectly), or we can fetch an "Assessment" from the "assessment" table "with" its corresponding "Farm" (in which case the timestamp is formatted correctly).

Farms schema:

export const farmsTable = pgTable(
  "farms",
  {
    id: uuid("id").defaultRandom().primaryKey().notNull(),
    ...
    createdAt: timestamp("created_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),
  },
  (table) => {
    return {
      ...
    };
  },
);

export const farmsRelations = relations(farmsTable, ({ one, many }) => ({
  assessments: many(assessmentsTable),
}));

Assessment schema:

export const assessmentsTable = pgTable(
  "assessments",
  {
    id: uuid("id").defaultRandom().primaryKey().notNull(),
    ...
    farmId: uuid("farm_id")
      .references(() => farmsTable.id)
      .notNull(),
  },
  (table) => {
    return {
      farmIdx: index("assessments_farm_idx").on(table.farmId),
    };
  },
);

export const assessmentsRelations = relations(assessmentsTable, ({ one, many }) => ({
  ...
  farm: one(farmsTable, {
    fields: [assessmentsTable.farmId],
    references: [farmsTable.id],
  }),
}));

Querying directly, the timestamps are in a readable but non-ISO format:
Query:

await this.dbClient.query.farmsTable.findFirst({
        where: (farm, { sql }) => sql`${farm.id} = ${id}::UUID`,
        with: {
          owner: true,
        },
        columns: {
          ownerId: false,
        },
      });

Result:

{
  "id": "13f109e2-13b8-497f-a515-4d99cc5630ee",
  "createdAt": "2024-03-24 15:03:29.127413",
  "updatedAt": "2024-04-04 10:29:59.38",
  ...
}

As a relation, the same timestamps are in the desired ISO format:
Query:

await this.dbClient.query.assessmentsTable.findFirst({
        where: (assessments, { sql }) => sql`${assessments.id} = ${id}::UUID`,
        with: {
          runs: true,
          createdBy: true,
          farm: {
            with: {
              owner: true,
            },
            columns: {
              ownerId: false,
            },
          },
        },
        columns: {
          pathwayId: false,
          createdById: false,
          farmId: false,
        },
      });

Result:

{
  "id": "13f109e2-13b8-497f-a515-4d99cc5630ee",
  "createdAt": "2024-03-24T15:03:29.127413+00:00",
  "updatedAt": "2024-04-04T10:29:59.38+00:00",
  ...
}

Expected behavior

Ideally all timestamps would be returned consistently in the ISO format, so that we can handle these consistently when displaying in the rest of our application.

Environment & setup

No response

[alexcroox]

I'm experiencing this too, pgTable column: timestamp('createdAt', { mode: 'string' })

When fetched as query with top level column it returns:
2024-05-10 01:22:33

but if it's a fetched as a relationship column (in "with" object) it returns:
2024-05-10T01:22:33

[davidchalifoux]

Just ran into this myself on drizzle-orm version 0.30.8.