Accepted (November 2024)
RapidPM serves users across multiple time zones (primarily UK, but potentially global). We needed to decide how to handle dates and times in the database and application.
Problems with local time storage:
- Users in different time zones see inconsistent data
- Daylight saving time causes ambiguity
- Sorting and filtering by date becomes unreliable
- API integrations become complex
Store all dates and timestamps in UTC in the database. Convert to local time only in the frontend.
Database:
- All
DATE and DATETIME columns store UTC values
- No timezone information stored (implicit UTC)
Backend (Python/Flask):
- All date operations use UTC
- API returns ISO 8601 format with
Z suffix: 2024-01-15T10:30:00Z
- Never convert to local time in backend
Frontend (Angular):
- Display dates in user's local timezone
- Use date pipes for formatting:
{{ date | date:'medium' }}
- Send dates to API in UTC
User enters: "January 15, 2024 2:30 PM" (London time, GMT)
↓
Frontend converts to UTC: "2024-01-15T14:30:00Z"
↓
API receives and stores: "2024-01-15T14:30:00"
↓
API returns: "2024-01-15T14:30:00Z"
↓
Frontend displays: "January 15, 2024 2:30 PM" (user's timezone)
- Consistency: All users see the same underlying data
- Simplicity: No timezone conversion logic in database queries
- Correctness: DST transitions handled correctly by frontend
- Interoperability: Standard format for API consumers
- Sorting: Date sorting works correctly across timezones
- Frontend complexity: Must handle timezone conversion
- Debug complexity: Raw database values are in UTC
- User confusion: If user sees raw API response
Existing data was migrated in November 2024:
Artefact.create_date and Artefact.update_date converted from VARCHAR to DATEProject.start and Project.end converted from VARCHAR to DATE- All values assumed to be UTC
Store datetime with timezone offset (e.g., 2024-01-15T14:30:00+00:00).
Rejected because:
- MySQL doesn't have native timezone-aware datetime
- Would require schema changes
- Overhead of storing timezone with every date
Store dates in each user's local timezone.
Rejected because:
- Users collaborating see different times for same event
- Daylight saving creates ambiguity
- Requires user timezone to be stored and tracked