A friend of mine recently asked me whether to store DateTime in UTC or with TimeZones within a database column and Why?
Thinking about it made me remember all the bugs I had to deal with over the years due to DayLight savings and TimeZone conversions.
I am writing this article to share some of my learnings on how to handle Time in software systems and links to a few blog posts I feel have done an amazing job explaining the complexities of dealing with DateTime.
I generally follow these guidelines when it comes to handling time:
- Dealing with timezones is highly complex.
- Local country authorities govern local timezones.
- Having Time in multiple timezones make it harder to reason about business logic.
- UTC is the time standard against which all the world’s timekeeping is synchronised (or coordinated). It is not, itself, a time zone but rather a transcendent standard that defines what Time zones are.
- Use UTC as my server’s time zone
- Use UTC as the timezone to store all DateTime in my database
- Deal with time zone presentation in my application’s layer
- Deal with time zone schedule jobs in my application’s layer
- Use ISO 8601 for API
This article has some good reasons to why use UTC to store Time
This one is to see why timezones are so complicated to manage
Where UTC might not be a good option