The Golden Rule: Store in UTC, Present in Local Time
If you remember nothing else from this article, remember this: database columns representing a specific point in physical time should always store values in UTC (Coordinated Universal Time). Localizing time should happen at the presentation layer (the frontend client).
Why Local Time Storing is Dangerous
Imagine storing a user's scheduled alarm as local time without any timezone metadata. If the user travels to a different country, or if Daylight Saving Time (DST) begins, the alarm will fire at the wrong physical moment. Even worse, during the DST transition back in autumn, an hour repeats itself—resulting in duplicate entries or missed jobs.
Understanding Timezone Offsets vs. IANA Identifiers
An Offset (e.g., +05:30 or -08:00) represents the difference in hours and minutes from UTC. A Timezone Identifier (e.g., Asia/Kolkata or America/New_York) represents the region and all its rules, including past, present, and future Daylight Saving adjustments.
Recommended Database Types
- PostgreSQL: Use TIMESTAMP WITH TIME ZONE (timestamptz). It converts input times to UTC and stores them internally, automatically converting them back to local times specified by the connection client.
- MySQL / MariaDB: Use TIMESTAMP instead of DATETIME. TIMESTAMP is stored in UTC internally and converted back and forth, whereas DATETIME stores the literal input value ignoring timezone configurations.