Prisma•6d ago

Handling @db.Date fields client side from different Timezones (Postgres)

We have a database where some tables have date only fields (DateTime @db.Date) while others are regular DateTime fields. When the server sends this data then javscript will create a new Date() for both since we don't have a date only primitive there. This causes the @db.Date field which is stored as 2025-04-20 to be understood as a UTC 00:00 date. Therefore if I'm in New York which is -4h the date will result in 2020-04-19T20.00.000Z and the data will shift by one day in something like a calendar. We COULD simply say that all dates that we receive client side we add the current timezones offset so that it will then be correctly parsed as 2020-04-20T00.00.000Z. Then we woulc correctly have the beginning of the DB's date in the local timezone to correctly sort in the entry, BUT then we would mess up regular DateTime dates. A "createdAT" timestamp from 14:00 local NY time will come back to the same client with a 18:00 time which is WRONG. So in short we do need some way to differenciate between how we parse DateTime and db.Date Dates. If we have the service for that entity client and server side then we could add a manual mapping client side to only parse the specific fields that we know are db.Date fields. But this is not a generic or maintainance low solution. I also don't want to resort to saving them as DateStrings since the Database shouldn't have to adjust (and lower) its capabilities because of the client and we would loose some possibilities that Dates can do which strings can't.

4 Replies

Prisma AI Help•6d ago

You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!

maxOP•6d ago

Or is the solution simply to adjust ALL dates when reading and writing by their current timezone? Any date that the client sends will add/subtract its timezone offset when writing to the DB. There is a gotcha for this solution that I'll mention after but is it still applicable or am I overlooking something Example for db.Date fields 1. Client picks date( date only) from date picker (this will likely be a date with hours,minutes at 0, so "2025-04-20T00.00.000Z-4" 2. We send this date by adding the timezone offset so it becomes "2025-04-20T00.00.000Z" ( basically same date and time with timezone cutt off) 3, Postgres saves this as a date only a db.Date "2025-04-20" 4. Client receives this date originally as "2025-04-20T00.00.000Z" which translates locally to "2025-04-19T20.00.000Z-4" but we now adjust for that offset again and convert it to "2025-04-20T00.00.000Z-4" to get the beginning of that date for the local timezone to make it compatible again with client code Example for regular DateTime fields 1. Client picks a date & time for something like an Event: "2025-04-20T07.00.000Z-4", so 7:00 at local timezone in New York 2. again this gets send and stored with the local offset adjusted for and results db side in "2025-04-20T07.00.000Z", so simply the same time but the offset removed (without the adjustment in the DB would be 11:00) 3. Client reads the DateTime from the server which as "2025-04-20T07.00.000Z" and converts it to "2025-04-20T07.00.000Z-4" The pitfall for this is that basically whichever date and time is saved in the db will be shown locally as the same date and time verywhere in the world. no matter if it was Daylight saving time or not or if youre in New York or in Zürich. This may not be what you desire as this effectively removes timezones alltogether and it could give the false sense of what times mean. If I'm in Switzerland and wanna see what time an employee started working in new York I will see that he started at 7:00 wherever they are located at and it doesn't mean that they started working what for me is 7:00. In our case thats fine but I still wanna ask you guys what the perfect solution to this problem would be. I don't see a generic way to handle this (differentiate between DateTime and db.Date) client side

Nurul•4d ago

Hey! So, the fundamental need is for the client to know whether a received date/time value originated from a Date-only field or a DateTime field, right? Can you configure your server-side serialization to send date/time values as strings in specific ISO 8601 formats that allow the client to differentiate. For @db.Date fields: Send only the date part as a string: "2025-04-20". For DateTime fields: Send the full ISO 8601 string including the time and timezone information "2025-04-20T18:30:00Z" or "2025-04-20T14:30:00-04:00".

maxOP•4d ago

@Nurul (Prisma) Yes thats exactly the desired behavior. How can I implement a general solution that doesn't require hand picking the date only fields and mapping them? Is that where @prisma/adapter-pg comes into play? to parse db.Date differently when reading from postgres? @Nurul I think I discovered a bug in prisma/adapter-pg: If before instanciating the Prisma Client i add my own parser like so:

import { types as pgTypes } from 'pg';

pgTypes.setTypeParser(1082, (val) => {
  console.log("my parser used");
  return `${val}`;
});

import { types as pgTypes } from 'pg';

pgTypes.setTypeParser(1082, (val) => {
  console.log("my parser used");
  return `${val}`;
});

This should make prisma convert Dates using this mapping function. However, the following code from prisma/adapter-pg/index.js line 107 makes it so that if there is a custom-parser (internally from prisma) that one is used instad of my overwrite. Is this the desired behavior?

 getTypeParser: (oid: number, format?) => {
      if (format === 'text' && customParsers[oid]) {
        return customParsers[oid]; //<----- here the prisma internal Date mapper is used instead of my global override
      }

      return types.getTypeParser(oid, format)
 },

 getTypeParser: (oid: number, format?) => {
      if (format === 'text' && customParsers[oid]) {
        return customParsers[oid]; //<----- here the prisma internal Date mapper is used instead of my global override
      }

      return types.getTypeParser(oid, format)
 },

Gaming

Programming

Handling @db.Date fields client side from different Timezones (Postgres)

Did you find this page helpful?