Jaymal Posts - Answer Overflow

Jaymal

•Created by Jaymal on 4/4/2024 in #help

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!

19 replies

Gaming

Programming