Timezone returned for `timetz` is incorrect when using `findMany`

giraffesyo commented 3 years ago

Bug description

When you query for a field that is of type timetz you get the wrong time back if the time stored in the database is any other timezone besides UTC. For example, I have the following data in a table called classes:

devidgmefdn94u=> select * from classes;
 id | day_of_week | class_time  | instructor | class_type | virtual 
----+-------------+-------------+------------+------------+---------
 19 |           5 | 11:30:00+00 |          3 |          7 | t
 20 |           5 | 11:30:00+00 |          3 |          7 | t
 48 |           1 | 12:00:00-05 |          3 |          5 | t

As you can see, the third row has a time of 12 PM Eastern time.

However, when you retrieve this time using the following code:

    const data = await prisma.classes.findMany()
    console.log(data)

You get the following:

[
  {
    id: 19,
    day_of_week: 5,
    class_time: 1970-01-01T11:30:00.000Z,
    instructor: 3,
    class_type: 7,
    virtual: true
  },
  {
    id: 20,
    day_of_week: 5,
    class_time: 1970-01-01T11:30:00.000Z,
    instructor: 3,
    class_type: 7,
    virtual: true
  },
  {
    id: 48,
    day_of_week: 1,
    class_time: 1970-01-01T12:00:00.000Z,
    instructor: 3,
    class_type: 5,
    virtual: true
  }
]

As you can see here, all three times say they are UTC, and the times have not been shifted/offset for the timezones they had in the database. That actually means that the time returned is wrong because now the timezone is lost.

This is a little bit related to #7915 however this is a different problem. That issue is about times always being stored in UTC.

How to reproduce

Create a timetz field
insert a record into the database that is not in UTC
use findMany to retrieve

Expected behavior

It should preserve the timezone that is in the database when giving the data.

Note that there seems to be a closed related issue #6384 but for timestamptz

Prisma information

The database structure I have is:

CREATE TABLE instructors (
    id serial,
    name text unique not null,
    image_url text unique not null,
    PRIMARY KEY (id)
);

CREATE TABLE class_types (
    id serial,
    name text not null,
    description text not null,
    duration integer not null,
    image_url text,
    PRIMARY KEY (id)
);

CREATE TABLE classes (
    id serial,
    day_of_week integer not null,
    class_time timetz not null,
    instructor integer not null,
    class_type integer not null,
    virtual boolean default true,
    PRIMARY KEY (id),
    FOREIGN KEY (instructor) REFERENCES instructors (id) ON DELETE cascade,
    FOREIGN KEY (class_type) REFERENCES class_types (id)
);

Environment & setup

OS: Mac OS
Database:
- Client: Postgres 13.3
- Server: PostgreSQL 12.7 (Ubuntu 12.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12) 5.4.0 20160609, 64-bit
Node.js version: v12.22.1

Prisma Version

prisma               : 2.25.0
@prisma/client       : 2.25.0
Current platform     : darwin
Query Engine         : query-engine c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt c838e79f39885bc8e1611849b1eb28b5bb5bc922 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : c838e79f39885bc8e1611849b1eb28b5bb5bc922
Studio               : 0.402.0