Drizzle Team•9mo ago

Timestamp formatted differently if fetched as relation rather than directly

Hi team, I'm using "drizzle-orm": "^0.30.7" with a PostgreSQL DB and postgres-js etc. 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. Querying directly, the timestamps are in a readable but non-ISO format:

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

{
  "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:

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

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

The schema for these two timestamp fields looks like this

  createdAt: timestamp("created_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),

  createdAt: timestamp("created_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true, mode: "string" }).defaultNow().notNull(),

Ideally everything would be returned in the ISO format, so that we can handle these consistently when displaying in the rest of our application. Any ideas if I'm missing something? Thank you!

11 Replies

JaymalOP•8mo ago

@Aaroned have you come across something like this before? Or know who may be the person to ask? @Dan Kochetov @alexblokh are you able to help with the issue above please?

alexblokh•8mo ago

Yep, that’s a bug cc: @Dan Kochetov

Dan•8mo ago

what do you mean "queried directly"? could you provide your queries in code?

JaymalOP•8mo ago

Thanks that's good to know. Do you have a triage process/a rough idea when we could expect a fix for it? Sure, 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).

Dan•8mo ago

could you provide your schema w/ relations and the query samples?

JaymalOP•8mo ago

Query for standalone farm:

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

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

Query for assessment with farm:

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,
        },
      });

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,
        },
      });

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 {
      ownerIdx: index("farms_owner_idx").on(table.ownerId),
    };
  },
);

export const farmsRelations = relations(farmsTable, ({ one, many }) => ({
  assessments: many(assessmentsTable),
  owner: one(usersTable, {
    fields: [farmsTable.ownerId],
    references: [usersTable.id],
  }),
}));

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 {
      ownerIdx: index("farms_owner_idx").on(table.ownerId),
    };
  },
);

export const farmsRelations = relations(farmsTable, ({ one, many }) => ({
  assessments: many(assessmentsTable),
  owner: one(usersTable, {
    fields: [farmsTable.ownerId],
    references: [usersTable.id],
  }),
}));

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],
  }),
}));

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],
  }),
}));

@Dan Kochetov I've put ... to hide fields which shouldn't be relevant. Hopefully that's enough for you to go on?

Dan•8mo ago

thank you could you also create an issue on GitHub and link it to this thread? just to keep track

JaymalOP•8mo ago

Cool - have created an issue here - https://github.com/drizzle-team/drizzle-orm/issues/2282. I've just linked to this thread atm, but I can copy in the detail if that's helpful?

GitHub

Issues · drizzle-team/drizzle-orm

Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - Issues · drizzle-team/drizzle-orm

Dan•8mo ago

yes, please copy it

JaymalOP•8mo ago

Done, I appreciate the support 🙂

francis•5mo ago

found the problem and updated the linked issue, fyi tl;dr is that the relational query system casts the rows to json, and postgres to_json on a timestamp converts it to ISO, where a plain select returns it in sql timestamp format

Gaming

Programming

Timestamp formatted differently if fetched as relation rather than directly