Allow passing YYYY-MM-DD in native database date types to avoid timezone issues

[rhufsky]

Bug description

When I insert a date value, it gets stored in the database date attribute with a day off by one.

How to reproduce

I create a date object via new Date(1960, 8, 24) and I insert it on both a date and a datetime attribute of a table record.

• In the date attribute xdate I find "1960-09-23" which to me is unexpected behaviour.

• In the datetime attribute xdatetime I find "1960-09-24" which I would expect.

• schema.prisma was generated by npx prisma db pull

import pkg from "@prisma/client";

const { PrismaClient } = pkg;
const prisma = new PrismaClient();

(async () => {
  // create a date 24.9.1960
  const now = new Date(1960, 8, 24);

  console.log(now);

  console.log(`Original date: ${format(now, "dd.MM.yyyy")}`);
  const name = "Demo";

  const nowFromDB = await prisma.myTable.create({
    data: { name: name, xdate: now, xdatetime: now },
  });
  console.log("Database date");
  console.log(nowFromDB);
  console.log(`Database date: ${format(nowFromDB.xdate, "dd.MM.yyyy")}`);
  console.log(`Database date: ${format(nowFromDB.xdatetime, "dd.MM.yyyy")}`);
})();

Expected behavior

I create a date object via new Date(1960, 8, 24) and I insert it on both a date and a datetime attribute of a table record.

• In the date attribute xdate I find "1960-09-23" which to me is unexpected behaviour.
• In the datetime attribute xdatetime I find "1960-09-24" which I would expect.

Prisma information

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model myTable {
  id        Int       @id @default(autoincrement())
  name      String?   @db.VarChar(50)
  xdate     DateTime? @db.Date
  xdatetime DateTime? @db.DateTime(0)
}

Environment & setup

• OS: MacOS 11.4
• Database: MySQL 8.0.25.
• Node.js version: v14.17.0

Prisma Version

2.24.1

[rhufsky]

After some research I understand the whole thing has to do with the fact that prisma always uses datetime with timezone support even if I want to store a date only. I think as MySQL (and PostgreSQL for that matter) support timezone-agnostic DATE datatypes it would be obvious that prisma supports these as well.

I have seen recommendations like storing a date as a string, using middlewares to convert and such.

Coming from Laravel/Eloquent and Sequelize I did not even expect that saving a date object can be a problem. I just want to store birthdays, start-of-work and end-of-work dates without thinking too much about that.

[chemicalkosek]

You can try to use Native Types: https://www.prisma.io/docs/concepts/components/prisma-schema/data-model#native-types-mapping
@db.Date

I had this issue since Prisma 1. I store hotel arrivals and departures as numbers, like: 20210605
I can then even search availability by using gt or lt.

Relevant issue: #4355

[rhufsky]

I already use native types, at least npx prisma db pull seems to generate these for me.
Surprisingly, when I insert the same date object into a @db.Date field, I get the off-by-one day and when I insert it into the @db.DateTime field, it seems to work in my sample, albeit with a time component shifted from 00:00 by the time difference of m timezone.

Even if this works in the sample, in my real app the insert into a @db.Datetime field creates an off-by-one again.

Maybe I resort to a string based date format bit that just does not feel right.

[chemicalkosek]

Oh sorry, I have just skimmed through your issue, didn't see you use native types.

I have once researched the subject.
There is one guy who seems to be an expert in date handling: https://stackoverflow.com/users/634824/matt-johnson-pint
You may run through his answers.
He was very sound about storing arbitrary dates like birthdays as Date without time and if that is not possible, use String or Number.
I don't remember now if there are any shortcomings of this solutions (storing as strings or numbers).
I don't have any issues so far, but your mileage may vary.

I agree that the best would be if it was handled by Prisma.

[rhufsky]

Thanks for the info. I feel that the whole date-time handling is surprisingly complex given the various variants how the Javascript Date() constructor handles timezones, console.log(date) handles timezones and there is no real Date datatype without timezones.

The most robust solution is probably to store dates as strings (YYYY-MM-DD) or numbers a you did. At least this gives you no surprises.

[chemicalkosek]

I believe it's still advisable to use the native Date type in database over String or Int, not sure though.
I made this decision 2 years ago and I can't remember why I chose Int over String.
Maybe in order to do database availability checks, but I think it would be possible with Strings too, not sure.
I search if a room is available in particular dates like this then:

     const isTaken = await ctx.prisma.booking.findFirst({
            where: {
              roomId: args.roomId,
              arrivalDate: {
                lt: args.departureDate,
              },
              departureDate: {
                gt: args.arrivalDate,
              }
            },
});

You should also know that there is a new spec coming to Javascript called Temporal: https://tc39.es/proposal-temporal/docs/
It should take away all the pains of the Date object and it will become the standard probably, even making all the date libraries obsolete.

It's currently in Stage 3, there are some polyfills already and you can test it (https://dev.to/romulocintra/temporal-date-time-in-javascript-today-23cb), soon to be available in browsers.

The main thing you are looking for is Temporal.PlainDate, which will handle your use case:
https://tc39.es/proposal-temporal/docs/#Temporal-PlainDate
https://tc39.es/proposal-temporal/docs/plaindate.html

[rhufsky]

Thanks for the update, Temporal seems to evolve into a great thing, will keep looking into it.

[pantharshit00]

Thanks for the discussion here.

I also went through the issue and it seems to me this is not a serialization fault in Prisma as we just call toISOString() in the Date object that we receive which the engines component can parse and send to the database.

We can potentially allow just passing YYYY-MM-DD in native @db.Date so marking this as an improvement.

[kazzkiq]

This is an issue most Prisma users will eventually stumble upon.

For example, I'm GMT-3 so if I do new Date("2022-04-12") it returns me Apr 11 2022 21:00:00. And if Prisma is forcing me to use new Date() every time I try to handle "YYYY-MM-DD" this would lead to really annoying timezone gymnastics in my code.

Prisma should not enforce JavaScript date type upon SQL Date fields exactly because of that.

Date types should definitely accept YYYY-MM-DD strings.

[Jon-Millent]

First you need to edit the schema.prisma file

  change_time  DateTime? @db.DateTime(0)

to

  change_time  String? @db.VarChar(0)

and run prisma generate. then you can pass a string to the time field

[dnguyenfs]

is there any plan to support this issue. I think this is the only big critical issue left in your awesome library.

[tkow]

If timezone offset not specified, UTC date is different from timezone date.
Maybe,

new Date(Date.UTC(2022, 9, 19))

it's safely used if storing specific date even if using any timezone because it is converted date object adapts timezone and stored as utc time.

(e.g. 1) 2022-10-19 if earlier X hours than utc time, the conversion

new Date(Date.UTC(2022, 9, 19)) [js timezone Object]-> 2022-10-19 (+ X hours from UTC)  [UTC conversion]->   2022-10-19:00:00:00(UTC stored in DB)

(e.g. 2) 2022-10-19 if later X hours than utc time, the conversion

new Date(Date.UTC(2022, 9, 19)) [js timezone Object]-> 2022-10-18 (- X hours from UTC)  [UTC conversion]->  2022-10-19:00:00:00(UTC stored in DB)

So, comparatively simple patches can be applied in javascript layer

type DIGIT =  0 | 1 | 2 |  3 | 4 | 5 | 6 | 7| 8 | 9
// type YYYY =  `${DIGIT}${DIGIT}${DIGIT}${DIGIT}` // too many literal types inferred error

type YYYY = number
type MM = `0${Exclude<DIGIT, 0>}` | `1${0 | 1 | 2}`
type DD = Exclude<`${0 | 1 | 2}${DIGIT}` | `3${0 | 1}`, '00'>

function parseUTCBasedDate(date: `${YYYY}-{MM}-${DD}` |  Date): Date {
  // if(typeof date === 'string') {
  //    const  [yyyy, mm, dd] = date.split('-')
  //   return new Date(Date.UTC(parseInt(yyyy, 10), parseInt(mm, 10) - 1,  parseInt(dd, 10)))
  // }
  // above code for arbitrary format 

  const _date = typeof date === 'string' ? new Date(date) : date;

  // If you want to apply new Date(2022, 9, 19) as saved 2022-9-19 in your timezone by parse(new Date(2022, 9, 19) )
  return new Date(Date.UTC(_date.getFullYear(), _date.getMonth(), _date.getDate()))
}

The codes restricts stored date to UTC. But there is one more consideration.
I'm not checking what parsed date data obtained from db later timezone than UTC. If the timezone used in javascript, it may be forward by one day. If do so, we further need patch if we use date object if we use date object for it .

/* strict
export function getTZDateFromUTC(dateField: Date) {
  return new Date(dateField.getFullYear(), dateField.getMonth(), dateField.getDate(), dateField.getHours() + (dateField.getTimezoneOffset() / 60))
}
*/
// better performance
export function getTZDateFromUTC(dateField: Date) {
  return new Date(dateField.getUTCFullYear(), dateField.getUTCMonth(), dateField.getUTCDate())
}

Now, I tested test code below.

/* eslint-disable @typescript-eslint/no-var-requires */
const { TestEnvironment } = require('jest-environment-node')

module.exports = class TimeZoneEnv extends TestEnvironment {
  constructor(config, context) {
    super(config, context)

    const timeZoneFromOptions =
      config.projectConfig.testEnvironmentOptions.timezone

    if (timeZoneFromOptions) {
      process.env.TZ = timeZoneFromOptions
    }
  }
}

/**
 * @jest-environment ./jest/test-date-environment.js
 * @jest-environment-options {"timezone": "Asia/Tokyo"}
 */
import { parseUTCBasedDate, getTZDateFromUTC } from './force-utc'

describe('conversion utc and tz', () => {
  it('store as UTC date', () => {
    const date = parseUTCBasedDate(new Date(2022, 9, 11))
    const date2 = parseUTCBasedDate('2022-10-11')
    expect(date.toUTCString()).toBe('Tue, 11 Oct 2022 00:00:00 GMT')
    expect(date2.toUTCString()).toBe('Tue, 11 Oct 2022 00:00:00 GMT')
  })
  it('restore as TZ date', () => {
    const tzdate = new Date(2022, 9, 11)
    const date = parseUTCBasedDate(tzdate)
    expect(getTZDateFromUTC(date).toUTCString()).toBe(tzdate.toUTCString())
  })
})

Now, we can control date type in all timezone. Maybe, these codes can be patched to @prisma/client codebase, too.
But, this cause is stored value cutoff time information, and it's big mistakes if only we can use UTC datetime.
Because it makes UTC time as fake date data and always need conversion mutually, These patches make anyone confused or annoyed .

If more strictly date type wanted derived from some databases, prisma should make DBs enable to configure other timezone.

As I thought it, date type should be stored yyyymmdd as short int or yyyymmdd as string as learning my experience like already mentioned when strict date type isn't needed. It makes code simple and have advantages about performance when indexing if it as numerical data.

[joshribakoff-sm]

I created a column:

DateTime @db.Date

The generated types in the Prisma client are:

Date | string

When I try to write the string there, I get a runtime error (and no type error):

Argument date: Got invalid value '2022-10-18' on prisma.upsertOneproduction_date. Provided String, expected DateTime.

At least the types should match what is actually allowed!

Using UTC / specifying timezone is a huge over complication, its simpler to just store it as a string. We shouldn't have to though, because Postgres supports dates (with no timezone) natively.

[Manubi]

Any news on this one? :)

[thetminko]

Prisma has been a pain when dealing with date and datetime. The Prisma team has been ignoring these date / datetime timezone issues for a long time. Probably, should just go back to Sequelize.

[joshribakoff-sm]

Fair but how hard is it to workaround? Can't you just use an int column and store a timestamp representing the date as a unix timestamp (UTC)? This would support sorting. Alternatively, you could also use a string column to workaround it.

[u007]

i think if prisma detect the field is "Date" only, it should not convert it to utc, it does not make sense

[nicholasvperry]

I am also having the same problem using sql server and SvelteKit. It seems to work fine when inserting the date into the database. When the date is retrieved on the server side it is correct, but once it hits the client side the date is incorrect. Only for the @db.Date not @db.DateTime

[olso]

Same with db.Time on Postgres

startDate DateTime @db.Date // yyyy-mm-dd
startTime DateTime @db.Time // hh:mm:ss
endDate   DateTime @db.Date // yyyy-mm-dd
endTime   DateTime @db.Time // hh:mm:ss

Results in

Argument startDate: Got invalid value '2023-04-01' on prisma.createOneEvent. Provided String, expected DateTime.
Argument startTime: Got invalid value '00:00:01' on prisma.createOneEvent. Provided String, expected DateTime.
Argument endDate: Got invalid value '2023-12-31' on prisma.createOneEvent. Provided String, expected DateTime.
Argument endTime: Got invalid value '23:59:59' on prisma.createOneEvent. Provided String, expected DateTime.

Also Int suggestions don't work for storing time in my opinion

00:00:01 would have to be converted to 000001 which becomes just 1

[wrknbuycnsmndie]

so, any suggestions on it? how you guys works with Date now?

[tomfa]

so, any suggestions on it? how you guys works with Date now?

Timezones are difficult, maybe extra so in Javascript.

An overview over what we (don't) do:

code	use?	comment	as ISO string (UTC)
new Date('2023-01-01T00:00:00Z')	✅		2023-01-01T00:00:00.000Z
new Date('2023-01-01T00:00:00')	❌	unstable based on system TZ	?
new Date('2023-01-01')	✅	for dates without time only	2023-01-01T00:00:00.000Z
new Date(2023, 0, 1)	❌	unstable based on system TZ. This is what OP did wrong	?
new Date(1672531200000)	🆗	not readable	2023-01-01T00:00:00.000Z
new Date(Date.UTC(2023, 0, 1))	🆗	verbose	2023-01-01T00:00:00.000Z

General tips

1. Set env var TZ=UTC in env var backend. This prevents unforseen issues when server is located in a different timezone.
2. Avoid using dates (without time), but when we do, pass it through API as YYYY-MM-DD.
3. Pass date with time through api as iso string YYYY-MM-DDTHH:mm:SSZ (new Date(...).toISOString()) .
4. Saving date-strings to prisma works fine when formatted as new Date("YYYY-MM-DD")

[nicholasvperry]

so, any suggestions on it? how you guys works with Date now?

I have two functions that I use to fix the date issue with prisma. I am using these for a sql server database. These are just for saving dates when you are saving date/datetimes that are not in UTC. One is for saving the date to prisma and the other is for using a date that you get from prisma. fixedDateToPrisma uses date-fns to cut off any time/timezone that is on the date you are sending and saving the date in the format prisma requires. This should properly convert your date. I hope this helps someone!

//fixed datetimes sent to database if database times are not in UTC
export function fixedDateToPrisma(date) {
return format(date, "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'");
};

//fix timezone issue from prisma if database times are not in utc
export function fixedDateFromPrisma(date) {
return new Date(date.substring(0, date.length - 1));
};

And here is an example of creating a post:

/** @type {import('./$types').RequestHandler} */
export async function POST({ request, response }) {
const { author, comment, area_id, date, shift, ip } = await request.json();
try {

    const createdPost = await prisma.TurnReport.create({
        data: {
            comment: comment,
            datetime: fixedDateToPrisma(new Date()),
            author: author,
            area_id: area_id,
            shift_date: date,
            shift_num: shift,
            ip: ip,
        }
    })
    await prisma.$disconnect()
    //return the post to get the id for adding attachments
    return json(createdPost)
} catch (error) {
    return new Response(JSON.stringify({ message: error.message }), { status: 500 });
}

}

And then this would be an example of using the date on a page somewhere. You would also need to import format from date-fns:

import { format } from 'date-fns';

let commentDateToCompare = (date) => {
return format(fixedDateFromPrisma(date), 'yyyyMMddHHmm')
}

If you are saving times in UTC as @tomfa mentioned then in your server.js file you need to make sure to set the timezone for your server to UTC

// Set the TZ environment variable to "UTC" to fix timezone issues
process.env.TZ = "UTC";

[alanpendleton]

This one issue is why I won't be using Prisma for my current project.

It's disturbing that it hasn't been addressed after more than 3 years. Issue #2783 dated 2020.06.17 was closed without the problem having been fixed.

[matthew-atomic]

@alanpendleton what are you using instead of Prisma? I'd like to move to another ORM because of this issue too.