Kysely•3mo ago

Issue with how timestamptz is returned depending on main or subquery

I'm running into the following issue where my date is returned in two different ways. Date is a timestamptz type column When I grab the lesson as followed:

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

export const getLessonByID = async (lessonID: number) => {
  const lesson = await db
    .selectFrom("lesson")
    .select((eb) => [
      "lesson.ID",
      "lesson.date",
      "lesson.index",
      Category.byID(eb.ref("lesson.categoryID")).as("category"),
    .where("lesson.ID", "=", lessonID)
    .executeTakeFirst();

  return lesson;
};

I receive "date": "2024-09-13T22:00:00.000Z" When I use a subquery like this:

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

export const Lesson = {
  byID: (lessonID: Expression<number>) => {
    const eb = expressionBuilder<Database, never>();

    return jsonObjectFrom(
      eb
        .selectFrom("lesson")
        .select(["lesson.ID", "lesson.date", "lesson.index"])
        .whereRef("lesson.ID", "=", lessonID)
    );
  },
}


export const getGroupByLessonID = async (groupID: number, lessonID: number) => {
  const group = await db
    .selectFrom("group")
    .select((eb) => [
      "group.ID",
      "group.name",
      "group.rank",
      "group.minTeachers",
      "group.maxTeachers",
      "group.hours",
      "group.timeframe",
      "group.paymentType",
      Lesson.byID(eb.val(lessonID)).as("lesson"),
    ])
    .where("group.ID", "=", groupID)
    .executeTakeFirst();

  return group;
};

I receive "date": "2024-09-14T00:00:00+02:00"

Solution:

Pretty sure pg just parses the JSON object without checking strings if they represent timestamps and it doesn't get any metadata related to the contents of the JSON. That'll be quite slow as a default, to check those strings.

Jump to solution

9 Replies

TheMelonAssassinOP•3mo ago

I think I've narrowed it down to it being part of the subquery, and potentially the json helpers

Igal•3mo ago

Hey 👋 That's a common issue not related to Kysely. Try https://www.npmjs.com/package/pg-types if using pg.

npm

pg-types

Query result type converters for node-postgres. Latest version: 4.0.2, last published: 9 months ago. Start using pg-types in your project by running npm i pg-types. There are 93 other projects in the npm registry using pg-types.

TheMelonAssassinOP•3mo ago

Huh really? It's a super weird thing, the same date from the same lesson returned in two different formats I'll check out the package to streamline the output, but I'm not sure how it happens in the first place

Solution

Igal•3mo ago

TheMelonAssassinOP•3mo ago

Hmmm so I was correct in my assumption that it only occurs in the subquery because of the parsing Where would you suggest I use pg-types?

Igal•3mo ago

Right before the spot that creates the pool. This'll slow down your result parsing, so look for a cheap heuristic that guesses "probably timestamp" before throwing in some heavy guns like regular expressions or a date library (e.g. date-fns, day-js, moment to try and parse the value).

TheMelonAssassinOP•3mo ago

Well obviously in the front-end it does not matter given that both are the same time. I stumbled upon in because of my test cases

Igal•3mo ago

Another option is to just use string | Date as the select type in your database interface.

TheMelonAssassinOP•3mo ago

Already was doing this I'll see how much it impacts my performance, and otherwise I'll rewrite the test cases Thanks for the help

Gaming

Programming

Issue with how timestamptz is returned depending on main or subquery