Allow shorter date format for native date type

[alonbilu]

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');

2. Introspect the database using: npx prisma introspect
3. Notice the datatype of the checkin_date field in schema.prisma was converted to DateTime
4. Executing the following query, will return an error.

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

5. 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

[cdock1029]

@alonbilu Related issues

#3447

#446

#1059

I had to downgrade to raw query and table construction, as date handling was nearly impossible as you've described here.

[pantharshit00]

Hello

@alonbilu Support for these types is in preview right now: https://www.prisma.io/docs/concepts/components/preview-features/native-types

You will need to add previewFeatures = ["nativeTypes"] to the generator block. After that when you will introspect the database, your schema will have those types:

model hotels {
  id           Int      @id @default(autoincrement()) @db.Integer
  checkin_date DateTime @db.Date
}

Then you can query the data like so:

One thing that we can change here is allowing shorter date format with native date type instead of asking for full ISO8061 format so I will keep this issue open for that.

[carchrae]

i would like to see this as well; being able to query by date is a pretty common need. the workaround in this issue and in prisma/docs#668 really doesn't feel right.

i would also ask that you consider adding a prisma type for Date (eg. 2020-30-03) rather than using DateTime as a one-size fits all. logically, Date is quite different, and represents a date regardless of timezone, rather than a timestamp/instant/milliseconds since UTC 1970.

here is one strange behaviour (perhaps a case that will inspire developers who crave consistency). on my database (postgres 12, prisma 2.19), when i query a table with a date column using prisma, it returns* a string (2020-03-03) as expected and desired. but if i want to query that same column, i have to pass in the string 2020-03-03T00:00:00.000Z - surely this is not the world we want to live in! 😸

(* edit: by "return a string", i am looking at the result of an api endpoint which uses res.json(prismaResults) - perhaps prisma returns a date object and it is serialised to date only? i admit i have not checked that)

another edit: the behaviour i just put a strike through is not accurate. i should have woken up more before replying. prisma always returns an iso datetime for both a date and timestamp column (eg 2020-03-03T00:00:00.000Z). prisma schema type is DateTime

[devuxer]

100% agree with @carchrae that there should be support for a "date without time" type. In Postgres, this is the Date data type. The intent is to store a date that is agnostic to time zone (e.g., "2021-05-05").

Not being able to simply use this string as an input makes life a lot harder and causes uncertainty. It's hard to feel confident about what will happen if I insert a date using new Date("2021-05-06") because the resulting Date object will get assigned the time zone of the machine/server running the code.

Will Prisma simply leave the year, month, and day component alone and stick that in the database, or will it try to convert to UTC (or something else)? I don't know, and that's the problem. Maybe it's documented somewhere, but it's a burden to even have to think about issues like this. If we could just insert "2021-05-06" as Postgres intended, it would bypass all of these issues.

[matthewmueller]

Do you know why this doesn't work?

const hotels = await prisma.hotels.findMany({
      where: {
            checkin_date: new Date('2021-02-03')
       }
});

This would be my preferred way to fix this problem. Let the Date object do the parsing for us.

In general, we're hesitant to add more acceptable date formats as strings because there's so many possibilities and overlap in how you parse these formats. Right now we just support ISO 8601 in string format (what you get back from event.toISOString()).

Re-labeling as a bug since you should be able to select a date type by using new Date.

Curiously if I do:

> new Date('2021-02-03')
2021-02-03T00:00:00.000Z

I get the valid date for the workaround that @alonbilu described.

[carchrae]

@matthewmueller - yes, perhaps there is a a bug converting a Date object into the query. most of us have many scars from timezone bugs - so maybe there is some bias for wanting to remove the time part and the chance for such bugs.

as to aiming for new Date('2021-02-03') as the only way to query a date, it is a bit tedious to have to check queries coming from, say a rest client, and then add special transformers (string to date) for certain fields. but i don't need to do that if i send a string with T00:00:00.000Z appended to it.

In general, we're hesitant to add more acceptable date formats as strings because there's so many possibilities and overlap in how you parse these formats. Right now we just support ISO 8601 in string format (what you get back from event.toISOString()).

so, the request is not for arbitrary formats. we'd just like support for that format for the date only version of ISO 8601, which maps to the postgres date only column type. https://en.wikipedia.org/wiki/ISO_8601#Calendar_dates

if the underlying column type is actually a date (with no time), it seems a bit odd that 2021-02-03T00:00:00.000Z works but 2021-02-03 does not. (no suggesting you break it for people who have applied the time as a workaround, just requesting you support the short version too)

[tobiasdiez]

It would be nice if Prisma supports the "plain" types of the new temporal proposal, i.e. PlainDate for dates without time, and partial dates such as PlainYearMonth and PlainMonthDay: https://tc39.es/proposal-temporal/docs/

[matthewmueller]

@tobiasdiez thanks for sharing. Let's hope this makes it in there.

I could see us accepting a date as a string in the case when you have @db.Date.

FYI, I'll also re-label this as a feature request because after reading @carchrae's comment I realized with new Date('2021-02-03') on the client, it potentially could be the previous or next day, depending on the server's timezone and the database's timezone.

[vimutti77]

@matthewmueller @db.Time should accept a time as string too.

e.g.

const store = await prisma.store.create({
  data: {
    openTime: '10:00:00',
    closeTime: '20:00:00',
  }
});

[tsongas]

Yeah I was born on Sept 18 and my birthday stored as @db.Date keeps coming back from Prisma as Sept 16 or 17 depending on where in the world the code is being run :/.

[capaj]

@tsongas I believe this happens when you use the official db driver as well. I certainly saw it happen with the pg package for me with Date sql type when using knex. Not sure if this is a problem of prisma.

[Gouni75]

Waiting for prisma taking into account Date as DateTime, I use in front moment(date).utc(true).format() to avoid timeZone offset.

[wtanna]

This was a pretty large issue for me that I was not aware of when I started to migrate my Rails API + Postgres to Typescript Express API + Prisma + Postgres.

The approach I took for now is to use Prisma middleware to allow my frontend to continue to send up "2022-01-27" and have the backend return back "2022-01-27".

It's a pain to have to do this on different types of collections and actions, but at least it makes it so if this feature comes in I can just delete the middleware and everything will hopefully work the same!

[janpio]

Can you share the middleware? Maybe some others can benefit from having a template to copy from.

[wtanna]

Of course! Sorry for not posting it earlier! I couldn't find any documentation in regards to working with the middleware in Typescript, so it's a bit rough but this is what I currently have:

interface TransactionArgsData {
  invoiceDate: string | Date;
}

interface TransactionArgs {
  data: TransactionArgsData;
}

interface TransactionParamsMiddleware extends Prisma.MiddlewareParams {
  args: TransactionArgs;
}

prisma.$use(async (params: TransactionParamsMiddleware, next) => {
  if (
    params.model === "Transaction" &&
    (params.action === "update" || params.action === "create")
  ) {
    if (params.args.data.invoiceDate) {
      params.args.data.invoiceDate = new Date(params.args.data.invoiceDate);
    }
  }

  const result = (await next(params)) as
    | Transaction
    | Transaction[]
    | undefined;

  if (!result) return result;

  if (params.model === "Transaction") {
    if (Array.isArray(result)) {
      return result.map((transaction) =>
        convertTransactionInvoiceDateToShortStringDate(transaction),
      );
    }

    return convertTransactionInvoiceDateToShortStringDate(result);
  }

  return result;
});

What's happening above is if the model that is about to run a query is a Transaction object, and the action is either update or create it means my frontend has sent up a short string of "2022-01-27". The backend needs to adjust that into a Date for Prisma which is what is happening with the new Date(). I could move the invoiceDate check into the first if statement as well, but I wasn't sure if I was going to have more logic for that model and actions, so I kept that first level if statement focused on that.

If the index or show route is hit, then the backend is either going to return back an array of Transaction objects or a single one. It might actually return nothing if the user goes to a show page of a transaction that doesn't exist. In which case the backend can return early.

If it's an array (index) then the backend needs to go through every object and convert the date into a short string. Otherwise, if it's a single object (show) then just call the transform function and return.

This solution is pretty brutal in regards to having to loop through every record and transform it before having to send it. But for my use cases it doesn't impact performance, as my data set isn't super large, your mileage may vary.

For even more context on how I use it in my app, I have all my model routes in individual files. I have a src/transactions.ts file that is imported in the src/index.ts file. I am then using express.Router to load in the route files

// other imports...
import * as transactionRoutes from "./transactions";

const apiRoutes = express.Router();
apiRoutes.use(cors());

// Transactions
apiRoutes.post(`/transactions`, transactionRoutes.createTransaction);
apiRoutes.get("/transactions/:id", transactionRoutes.getTransaction);
apiRoutes.get("/transactions", transactionRoutes.getTransactions);
apiRoutes.delete(`/transactions/:id`, transactionRoutes.deleteTransaction);
apiRoutes.put("/transactions/:id", transactionRoutes.updateTransaction);

// other routes...

// App setup
const app = express();
app.use(express.json());
app.use("/api", apiRoutes);

const port = process.env.PORT || 3001;
app.listen(port, () =>
  console.info(`🚀 Server ready at: http://localhost:${port}`),
);

the above middleware is in the src/transactions.ts file. It seems like since the middleware is in that file it's only being called when the above /transaction routes are being run, versus all of the routes. So I might be able to DRY up the code a bit and remove the Transaction check. However, I'd rather be more cautious and make sure that I'm only changing around an object that has the schema I'm trying to transform.

This was a lot of information, but I hope it helps!

[joshuakcockrell]

I want to affirm that DATE and DATETIME really are different types, and they need to be supported differently in a prisma schema.

They are entirely different types in MySQL and PostgreSQL, and there's a very good reason for this. Let's use storing a user's legal date of birth as an example:

DATETIME - An amount of time since UTC 1970
If I make my user pick new Date("1990-05-20") as their dob, timezone is BUILT IN to this object. Depending on what timezone their web browser was in when the object was created, and what timezone my server is running in, and what timezone they were born in, this DATETIME object WILL be interpreted differently. The only way it would ever make sense to use DATETIME to store this data is if the user was born, input their dob, and had the server running in the same exact timezone.

DATE - A literal YYYY-MM-DD string
DATE has no concept of time zone. The "1990-05-20" string means "May 20th, 1990" no matter where in the world my server is running at, or where my user was when they entered it into a web browser, or what timezone they were born in.

[joshuakcockrell]

The desired outcome would be to support this in our schema:

model User {
  dateOfBirth String @db.Date
}

Instead of the current behavior:

model User {
  dateOfBirth DateTime @db.Date
}

[maxh]

For what it's worth, I'd like to chime in with the ideal dates and times my team would like support for in Prisma.

We use the terminology on https://www.w3.org/International/articles/definitions-time. Below are the serialization formats for dates and times throughout our system (GraphQL, DTOs, REST API, etc). These are similar to the types suggested by @joshuakcockrell, with further breakdown of incremental vs wall time vs zoned wall time.

Persistence is brittle due to limited support for dates and times in Prisma. Hopefully this can be addressed in future iterations. I had been planning to use type aliases as a work-around, but it seems they are being removed #9939. So I'm not sure how best to proceed.

Update: This seems like it would support our use case well: #5039

UtcTimestamp

• A point in incremental time with millisecond resolution.
• Example: "2022-05-23T23:12:05.123Z" (string)
• Usage: database and system times (createdAt, revisedAt, etc).
• Always includes the Z at the end indicating UTC offset of 0.
• Persist with PostgreSQL’s timestamp(3) type.

FloatingDate

• A floating date, unanchored to a specific time zone or offset.
• Example: "2022-04-23" (string)
• UTC offset is always undefined. Could be paired with time zone; see below.
• Usage: domain-specific dates like pick up dates, invoice issue dates.
• Persist as date- or string-type column in PostgreSQL.

FloatingDateTime

• A floating date and time with millisecond resolution, unanchored to a specific time zone or offset.
• Example: "2022-05-23T06:00:00.123" (string)
• UTC offset is always undefined (i.e. no Z). Could be paired with time zone; see below.
• Usage: domain-specific date times. For example, delivery appointment window for a warehouse could be represented with two of these (9am to 11am on May 5th 2021).
• Persist as timestamp- or string-typed column in PostgreSQL.

WallDate

• A wall time date.
• Example: "2022-04-23|America/Chicago"
• UTC offset can be computed via IANA table lookup.
• Serialize and persist as a single string for ease-of-use.

WallDateTime

• A point in wall time with millisecond resolution.
• Example: "2022-05-23T06:00:00.000|America/Chicago"
• UTC offset can be computed via IANA table lookup.
• Serialize and persist as a single string for ease-of-use.