Data and Business Systems
Model dates and time zones before calendar features
Calendar features need more than a timestamp column. Distinguish dates, local appointments, global instants, and recurring rules so a change of location or daylight-saving offset does not quietly change the business commitment.
A delivery date, a meeting time, and a daily opening hour look similar on a calendar. They describe different commitments. Treating them as one generic timestamp forces hidden assumptions into reminders, reports, and integrations. Decide what users mean before choosing storage types or drawing the first calendar screen.
Separate four kinds of meaning
A date-only commitment names a calendar day, not a moment. An instant identifies one occurrence on a shared timeline. A local appointment names wall-clock time in a place. A recurrence describes repeated local behavior. Ask which meaning each field carries and whether the commitment follows the place or the viewer.
PostgreSQL provides distinct date, time, timestamp, and interval types.[5] Those choices help represent values, but they do not replace the product decision. An interval between events is not automatically equivalent to a recurring appointment at the same local hour.
Keep the zone when the rule needs it
For timestamp with time zone, PostgreSQL stores the value internally as UTC and does not retain the originally stated zone.[5] If the business needs the appointment location or a future local recurrence, retain that zone separately. Do not expect the timestamp alone to preserve the original scheduling intention.
Recommend named time zones for place-based rules and an explicit policy for daylight-saving transitions. For a repeated local time, define which occurrence the user means. For a time skipped by a clock change, define the response. Ask the user or apply a documented business rule rather than silently guessing.
Hypothetical example: a regional training schedule
A hypothetical training provider offers a session at nine in the morning at the hosting office. A remote participant sees the equivalent local instant. The office schedule retains its named zone, while each participant chooses a display zone. The invitation shows both the office reference and the participant view.
For a recurring session, the provider defines whether nine remains the office time when seasonal offsets change. This requires additional recurrence rules and testing. It avoids turning a convenient display conversion into an unintended change to the commitment itself.
Make boundaries visible to operators
Show the relevant zone near appointment inputs, confirmations, and exports. State which zone determines a report day or a deadline. Define whether an end boundary is included. These details matter when one team finishes work while another team has already entered the next calendar day.
Complexity should follow actual need. A local office holiday list may require only dates. Do not add artificial midnight timestamps just to fit a shared component. Conversely, avoid a date-only field for an exact cross-region deadline. Review the classification whenever a feature expands to new locations. Ask operators to confirm a cross-zone invitation and its edited version during acceptance testing.
- Label each field as date, instant, local appointment, or recurrence.
- Retain a named zone for place-based scheduling rules.
- Define behavior for skipped and repeated local times.
- Specify report-day zones and deadline boundaries.
- Test display, export, reminder, and edit round trips.
