Until recently I always thought I had a decent grasp of timezones. It turns out my previous assumptions were reasonable but not practical. I thought:
- Saving/using a numerical timezone offset (e.g.
-0600) was "good enough"
As it turns out: nope and nope.
Timezones are typically based on geographical locations. For example, we have the IANA timezone
America/Chicago which can represent Central Time for the United States.
Depending on the time of year, this will be a numerical offset of
-0600 from UTC. The change depends on the United States definition of daylight saving time. For example:
2016-01-01T00:00:00Z + America/Chicago -> 2016-01-01T00:00:00-0600 2016-04-01T00:00:00Z + America/Chicago -> 2016-04-01T00:00:00-0500
Unfortunately, regions can share the same offset (e.g. another country North/South) so if we save/restore the data, then we won't know what country the original datetime corresponded to.
2016-04-01T00:00:00Z + America/Chicago -> 2016-04-01T00:00:00-0500 2016-04-01T00:00:00Z + America/Mexico_City -> 2016-04-01T00:00:00-0500
In most scenarios, this can be slipped under the rug but if you want to let users create/edit timezone-aware datetimes, then it won't work.
Additionally, in PostgreSQL all datetime values are stored in UTC with no preservation of IANA timezone. As a result, we must save both the UTC value and the IANA timezone in 2 separate columns.
Date implementation, there is support for numerical offsets but not for IANA timezones. As a result, we need to perform some guess work. Thankfully there are a few solutions:
- Guess based off of the user's IP address
- Guess based off of the browser's timezone offset
Here are some resources I found practical when reading up on timezones: