Documentation for the policies for handling datetime.
Date vs Timestamp
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.
The following fields should be date but are grandfathered in as timestamps.
Model | Field |
|---|---|
Activation_CR | startDate |
Reimbursement | dateCreated |
Reimbursement_Request | dateOfExpense |
Task | deadline |
Work_Package | startDate |
Front-End
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 or UTC (and make sure you convert when sending to backend if necessary)!
When displaying a timestamp that includes a time, include the time in ET (TICKET: Add a transformer Date → string with ET at the end, for view only)
Back-End
Endpoints dealing with timestamps should send as yyyy-mm-ddThh:mm:ss.sssZ or similar. Axios and Express so 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.
TICKET: 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 00:00:00 (midnight UTC).
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.