Raw query returns timestamp as string.

[hollow schooner]

Hi,

I'm using TypeOrm, i'm executing a raw query, but the one of the date column is being returned as string, so TypeOrm or node is not converting it to UTC as it would if i call a query using TypeOrm methods or queryBuilder, what am i doing wrong here?

using PostgreSQL

    async activeShift(userId: number, { date }: SingleDateQuery) {
        const startDate = startOfDay(date);
        const endDate = endOfDay(date);

        const queryToExec = `
        SELECT s.id, s.name,
                (SELECT COALESCE(json_agg(json_build_object('created_at', a.created_at, 'type', a.type)), '[]')
                FROM attendance a
                WHERE a.shift_id = s.id
                AND a.user_id = us.user_id
                AND a.created_at BETWEEN :startDate AND :endDate) AS actions
        FROM user_shift us
                LEFT JOIN
            shift s ON us.shift_id = s.id AND s.deleted_at IS NULL
                LEFT JOIN
            attendance a ON s.id = a.shift_id AND a.user_id = us.user_id AND us.deleted_at IS NULL
        WHERE us.user_id = :userId
        GROUP BY s.id, us.user_id, s.start_time
        ORDER BY s.start_time;`;

        const queryParams = { userId, startDate, endDate };

        const { query, params } = convertToQueryWithParameters(queryToExec, queryParams);

        return await this.userRepo.query(query, params);
    }

// result
[
  {
    "id": 8,
    "name": "Morning",
    "actions": [
      {
        "created_at": "2023-10-12T22:12:53.986+05:30",
        "type": "CHECK_IN"
      }
    ]
  },
  {
    "id": 9,
    "name": "Night",
    "actions": []
  }
]

created_at us getting converted from 2023-10-12 16:42:53.986000 +00:00 to 2023-10-12T22:12:53.986+05:30, but not to the UTC without offset, like 2023-10-12T16:42:53.986Z.

[lime lance]

can you show us the entity?

[hollow schooner]

@lime lance

actually i managed to get the same result using createQueryBuilder

const activeUseShifts = await this.userShiftRepo
  .createQueryBuilder("user_shift")
  .select(["shift.id", "shift.name", "shift.startTime", "shift.endTime", "attendance.createdAt", "attendance.type", "user_shift.id"])
  .leftJoin("user_shift.shift", "shift")
  .leftJoin("shift.attendances", "attendance", "attendance.user = :userId AND attendance.createdAt BETWEEN :startDate AND :endDate", {
    userId,
    startDate,
    endDate,
  })
  .where("user_shift.user = :userId", { userId })
  .orderBy("shift.startTime")
  .getMany();

// result
[
  {
    "id": 48,
    "shift": {
      "id": 8,
      "name": "Morning",
      "startTime": "09:30:00",
      "endTime": "18:30:00",
      "attendances": [
        {
          "createdAt": "2023-10-12T16:42:53.986Z",
          "type": "CHECK_IN"
        }
      ]
    }
  },
  {
    "id": 49,
    "shift": {
      "id": 9,
      "name": "Night",
      "startTime": "17:30:00",
      "endTime": "02:30:00",
      "attendances": []
    }
  }
]

here are my entities

// shift.entity.t
@Entity()
export class Shift extends BaseEntity {
    @Column()
    name: string;

    @Column({ type: "time" })
    startTime: Date;

    @Column({ type: "time" })
    endTime: Date;

    @Column({ default: true })
    isActive: boolean;

    @Exclude()
    @ApiHideProperty()
    @OneToMany(() => UserShift, u => u.shift)
    userShifts: UserShift[];

    @ApiHideProperty()
    @OneToMany(() => Attendance, a => a.shift)
    attendances: Attendance[];
}

// attendance.entity.ts
@Entity()
export class Attendance extends BaseEntity {
  @Column({
    type: "enum",
    enum: AttendanceType,
    default: AttendanceType.CHECK_IN,
  })
  type: AttendanceType;

  @ManyToOne(() => User)
  user?: User;

  @ManyToOne(() => Shift)
  shift: Shift;

  @Column({
    type: "point",
    transformer: {
      to(value: IGeolocation): string {
        if (!value) return null;
        return `(${value.latitude}, ${value.longitude})`;
      },
      from(value: { x: number; y: number }): IGeolocation {
        if (!value) return null;
        return { latitude: value.x, longitude: value.y };
      },
    },
    nullable: true,
  })
  coordinates: IGeolocation;
}

// user-shift.entity.ts
@Entity()
export class UserShift extends BaseEntity {
  @ManyToOne(() => User, (u) => u.activeShifts)
  user: User;

  @ManyToOne(() => Shift, (s) => s.userShifts)
  shift: Shift;

  @Column({
    type: "point",
    transformer: {
      to(value: IGeolocation): string {
        if (!value) return null;
        return `(${value.latitude}, ${value.longitude})`;
      },
      from(value: { x: number; y: number }): IGeolocation {
        if (!value) return null;
        return { latitude: value.x, longitude: value.y };
      },
    },
    nullable: true,
  })
  coordinates: IGeolocation;
}