Background
Suppose you have an app (mobile or web), often you want to display the time in the local time of your end user. However,your database still needs to store the time. While a common scenario, several questions often come up:
- How should you store users’s timezones in your database?
- When do you convert it to local time?
- Assume you have an API between your client and backend, how should the timestamps be represented?
- What format would datetime be stored in database?
How does the database store datetime
Databases are designed to store dates efficiently minimizing space while making queries as fast as possible. Because integers are easier to query, index, and more space efficient than strings, dates are usually stored as 64-bit integers such as a unix epoch with milliseconds. As a user of the database, you usually don’t need detailed information on implementation details and thus can leverage the database’s provided date or datetime data type. There shouldn’t be a reason why you would store a date as a raw string or bigint.
Databases will convert any datetime into a UTC epoch to store internally. However, some databasesmay enable storing timezone information. If that’s the case, it’s recommended to convert all dates to UTC before storing.
Don’t use local timezone. Otherwise, you’ll be pulling your hair out when your database is deployed in high availability designs across multiple data centers across multiple timezones.
What format to use in the APIs
Front end clients such as mobile apps and single page apps require communicating with your backend via APIs, and those APIs may have datetime fields. Like the database, to keep your API consistent, HTTP responses payloads should always be in UTC. However, your API should follow the robustness principle: Be conservative in what you do, be liberal in what you accept from others. Thus, your API should parse any timezone in the HTTP request body or URL parameters and not require UTC. Some APIs will default the absence of timezone information as UTC.
Unlike most databases, there is no native datetime data type in JSON thus dates will have to be stored as JSON number or a JSON string. Either works, but standards should be followed meaning a date string should be formatted as one of the ISO 8601 formats. Date numbers should be stored as Unix Epoch time with the API contract specifying seconds or milliseconds.
There is a split opinion between these two approaches. Twitter, DropBox, Segement.io APIs use ISO 8601, while Chargebee, Pusher and Stripe APIs use Unix Epoch Time.
We at Moesif prefer ISO 8601 (yyyy-MM-dd'T'HH:mm:ssZ) because it’s human readable and has a specified timezone. There is no ambiguity if the epoch is in seconds or milliseconds. ISO 8601 strings are encoded in a way that enable decent string sorting and comparison.
Epoch has benefits over ISO 8601 also. If you’re dealing with high volume sensor data or APIs for IoT devices, you may want epoch since it enables smaller payloads without accounting for compression and more familiar with those coming from an IoT/embedded background.
Storing user’s timezone and displaying in local time
First, decide if you need to store user’s timezone.
If your requirement is to only display the datetime in the user’s local time in your app, you do not even need to store the user’s timezone. Browser javascript and mobile APIs enable datetimes to be converted and pretty printed according to the user setting’s timezone and locale.
However, you may discover you later will perform backend processing that initiates communication with your user such as a Slack alert or email. In this case, you may end up caching the user’s timezone/locale information in your database. Thus you can convert and pretty print any datetimes before that alert or email is sent.
Avoid pitfall of storing the UTC offset, as the offset number can often change due to things like daylight savings time.It’s recommended to store the timezone identifier such as America/Los_Angeles.There is a full list of time zones which most datetime libraries natively support.
Summary
Manage different timezones if you have a global application or API can seem challenging at first. This article basically outline the strategy of keep it simple, and just work with UTC time at all times until right before display to the user.
Top comments (0)