Date/Time in DB & API: Time Zone Aware & UTC

[jacob-a-brown]

Decision

All date/time objects & fields are to be time zone aware and stored/handled in UTC.

Benefits

This greatly simplifies both the API and storage because

• It allows datetime comparisons without having to worry about varying time zones.
  • This enables, for example, easy comparisons to the present date/time and between different objects.
• It means that we do not need to worry about daylight savings time.

Implementation

Backend

• Pydantic

  • All date/time fields should be AwareDatetime fields (which is imported from Pydantic).
    • This is applicable to all schemas (i.e. Create, Update, Response, and others used for inheritance).
  • field validators should be used for all date/time fields for Create and Update schemas to convert them to UTC if they were not already provided in UTC. This offers a layer of protection and certainty.

• Database

  • All date/time fields should set timezone=True (e.g. DateTime(timezone=True) and TIMESTAMP(timezone=True)).
  • All auto-generated date/time values should use that use the service_default=func.timezone("UTC", func.now()) to ensure the recorded values are in UTC.

Frontend

Because all date/time fields will be converted to UTC if not already provided as UTC, the frontend can in theory send data in any time zone. Nevertheless, to help ensure data validity and quality the frontend should always send date/time info in UTC.

The frontend will receive all date/time information in UTC, so it must convert it to the appropriate time zones for display and interaction (likely Mountain Time for NMBGMR products).