[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.

[fnimick]

Still happening with 0.32.1 with postgres-js. It also differs from the top level of the relational query to any included entities.

direct select of datetime [ { createdAt: '2024-08-09 21:47:00.686191+00' } ]
top level datetime 2024-08-04 01:04:14.718633+00
one level nested datetime 2024-08-05T17:24:00.757786+00:00
two levels nested datetime 2024-08-09T21:47:00.686191+00:00

[fnimick]

I've found the problem. The issue is the single query being generated for relational queries using an intermediate json representation, and converting a date to json converts to ISO.

here's the relevant part of the generated query:

select <top level fields>, "mytable_relatedTables"."data" as "relatedTables" from "mytable" "mytable" left join lateral (select coalesce(json_agg(json_build_array("pathtecOrder_fulfilledTestKits"."id", <related table fields>)), '[]'::json) as "data" from "relatedtable" "mytable_relatedTables" where "mytable_relatedTables"."my_table_id" = "my_table"."id") "mytable_relatedTables" on true

You can see this behavior for yourself - run a select to_json(timestamp_field) from table; in psql.

There are likely to be similar problems for any other data types used that are changed as a result of a to_json call in postgres.