Allow shorter date format for native date type

Open
#4,355 25 comments 60 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
35/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
node.js, postgresql, typescript
Domain
databases

Research direction

Start by reproducing the PostgreSQL date table and running npx prisma introspect, then compare the generated schema.prisma type with the documented query behavior for YYYY-MM-DD and Date values. Investigate the introspected mapping and filtering path; done means PostgreSQL Date and Time types are supported without the reported invalid-value or no-results behavior.

Written by the indexing model from the issue text.

Description

kind/feature topic: @db.Date topic: dates / DateTime topic: native database types

Bug description

If my PG table has a field of type Date (https://www.postgresql.org/docs/9.1/datatype-datetime.html), when calling prisma introspect this type will be converted to DateTime in the prisma.schema.

Later when I try to query the table through Prisma and send the value YYYY-MM-DD to the related Date field, Prisma responds with the following error:

Got invalid value '2020-12-30' on prisma.findManyitems. Provided String, expected DateTimeFilter or DateTime._

If I try to wrap the date-string in new Date( , prisma converts the date to the following format: YYYY-MM-DDT00:00:00.000Z`
However, when this new value is compared to the value that exists in the database, it is NEVER EQUAL due to the difference in the formats.

How to reproduce

  1. Create a new PostgreSQL table
CREATE TABLE public.hotels (
	id serial NOT NULL,
	checkin_date date NOT NULL
);

INSERT INTO public.hotels 
(checkin_date)
VALUES ('2021-02-03');
  1. Introspect the database using: npx prisma introspect
  2. Notice the datatype of the checkin_date field in schema.prisma was converted to DateTime
  3. Executing the following query, will return an error.
const hotels = await this.prisma.hotels.findMany({
      where: {
            checkin_date: '2021-02-03'
       }
});
  1. Executing the following will return no results:
const hotels = await this.prisma.hotels.findMany({
      where: {
            checkin_date: new Date('2021-02-03')
       }
});

Workaround

const hotels = await this.prisma.hotels.findMany({
      where: {
            checkin_date: '2021-02-03' + 'T00:00:00.000Z'
       }
});

Expected behavior

Prisma should have matched the date to the one in the database and return the record.

Please add support in Prisma for both Date and Time PostgreSQL data types

Environment & setup

  • OS: Debian
  • Database: PostgreSQL
  • Node.js version: 14.13.1
  • Prisma version:
@prisma/cli          : 2.11.0
@prisma/client       : 2.9.0
Dominant language
TypeScript
Stars
47.6k
Forks
2.5k
Avg merge
21h 59m
Merged PRs (30d)
95

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from prisma/orm

All issues in prisma/orm

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.