...
When designing schemas, decide how to represent the date.
Date: store only date data. Should appear the same to everyone regardless of timezone. ex. 1/1/2024.
Timestamp/datetime: store an exact timestamp of date and time. ex. 1/1/2024 00:00 UTC. Does not store timezone info, e.g. 1/1/2024 03:00 UTC is NOT distinct from 12/31/2023 23:00 EDT.
The following fields should be date but are grandfathered in as timestamps. (TODO: Check if incomplete)
Model | Field |
|---|---|
Activation_CR | startDate |
Reimbursement | dateCreated |
Reimbursement_Request | dateOfExpense |
Task | deadline |
Work_Package | startDate |
...
If user has to input date/time manually, use MUI Date and Time Pickers. As a rule of thumb, including only a date picker should save date only, and including both should save timestamp.
Remember to document and indicate on the front-end whether a time picker is for EST ET or UTC (and make sure you convert when sending to backend if necessary)! By default it is a good idea to keep things in ET (America/New_York). Refer to the MUI documentation to set timezones.
When displaying a timestamp that includes a time, include the time in ET and append “ET” or “Eastern” after it. Don’t think there are examples of this currently, but if added you should make a new pipe for it.
Back-End
Endpoints dealing with timestamps should send as yyyy-mm-ddThh:mm:ss.sssZ or similar. Axios and Express so do this automatically with JS Date objects. (TODO: see if frontend and backend timestamps are sent in exactly the same format)
Endpoints dealing with dates only should send yyyy-mm-dd only to avoid confusion.
Auto-generated timestamps (e.g. work package created) should be generated on the backend, either with new Date() or, in the Prisma schema, @default(now()). This makes the timing more consistent and adds a layer of security also.
TODOTICKET: Add validators and transformers in both frontend and backend to convert between Date objects and plain date strings.
Prisma
Prisma by default stores dates as timestamps without timezone info. We assume all timestamps in the database are in UTC.
For future schema additions, explicitly tag timestamps with @db.Timestamp(3) and dates with @db.Date. (TODO: Do we need timestamps to contain timezone info? i.e. is 5 pm ET different from 4 pm CT? Probably not.)
For old data that is @db.Timestamp but should be a plain date, store it as yyyy-mm-dd 00ddT00:00:0000Z (midnight UTC). Use the transformer mentioned above.
Testing
A good idea to include manual tests for dates and times that involve times within 4 hours of midnight in either direction to make sure the feature doesn’t mess up the time displayed. Remember that the default timezone is ET, so it should at least work as expected in this time zone.
To debug timezone issues, check the network traffic (browser dev tools > Network) and your local database contents for the way the dates are formatted.