How to Handle Database Timezones

Working with timezones can be one of the hardest things in a database.

In this guide, you’ll learn:

• Why timezones are hard to work with
• A rule that covers almost all situations
• An exception to this rule

Let’s get into it.

 

What is the Issue?

Whenever you want to store date and time information in a database, you use a column for it. One example is a “created datetime” of a particular record.

It’s 10AM on March 25th 2021, and you create the record. It gets inserted into the table with a value like this:

2021-03-25 10:00:00

However, one factor that makes this complicated is timezones.

This process of inserting a datetime is simple if you’re developing on your own computer, where the server (your computer) and you are in the same timezone.

It gets a little more complicated if the servers are in different timezones. This is common, especially if you’re hosting on AWS or GCP or Heroku or any other hosting service.

Let’s say you insert the same data on your database server, which is on AWS on the US East Coast. The time there is 5 hours behind UTC, or UTC -5. You’re in Australia, where the timezone is UTC +10, which is a 15-hour difference.

But, the 10 AM time you inserted… is that 10 AM in Australian time, or 10 AM in US East time, or 10 AM in UTC time?

There’s no way to tell by looking at the data. You can look at the code that performed the insert and make some calculations.

So, the first issue is:

Date and time values stored in the database may not be clear about which timezone it refers to.

Another issue with working with timezones is if multiple users are in different timezones.

Say we have that same record above in our table, the one that has a time of 10AM.

2021-03-25 10:00:00

Our teammate is also a developer, but is based in India. The timezone there is UTC + 5:30.

So, what should happen to this 10AM time? If they look at it, do they think it’s 10 AM Indian Standard Time, or some other timezone?

This is the second issue:

Multiple users in multiple timezones can cause dates and times to be unclear

The third issue is related to daylight savings.

Let’s say you’re in Australia, and it’s currently daylight savings time. The timezone is UTC +11.

You insert a record with a datetime of 10 AM.

2021-03-25 10:00:00

In several months, when daylight savings time stops, your timezone changes from UTC +11 to UTC +10. You read the data from the database, but you’re not sure if the 10AM time is daylight saving time or standard time.

Is it standard time because you’re in standard time now? Or is it daylight saving time because it was daylight saving when you added the data?

So, the third issue is:

Daylight saving time can cause issues when storing and reading times

Fortunately, people have been working with databases for long enough to know that there is a solution to this.

Storing dates and times in this guide uses examples of a “point in time”, which is a single time when something has or will occur. This could be a created date for a user in the system, a log record, an event start time, or a payment processing time, for example.

This could be a time in the past or the future.

Another type of time to store is a time expression, such as “9AM every Tuesday”. This can’t easily be represented in a standard data type, and it’s relative to the person who specifies it. It’s common with task management or calendar applications.

 

Definitions

There are a few definitions you should know when working with timezones. I’ve used these terms in this guide and you might read them in other articles as well.

• UTC: Stands for Coordinated Universal Time, it’s the time standard that world clocks go by.
• Timezone: an area of the world that follows a specific time. They each have names, such as “US Eastern Standard Time”, or EST for short.
• Timezone offset: The number of hours that a timezone’s time is ahead of or behind UTC time. Represented as a number and a sign, such as -5 or +11.
• Daylight savings time: an offset of time to the standard time that applies temporarily to a timezone.
• GMT: Stands for Greenwich Mean Time, a time that is similar to UTC but has some differences.

This interesting article explains some misconceptions that we may have about time and its rules: Falsehoods programmers believe about time and time zones.

Let’s take a look at a couple of options and then the ideal solution.

 

Option 1: Store Timezone of User who Inserts Data

One option is to store the timezone of the user who inserts the data.

This could be within the same column (as there are data types that store timezone information), or a separate column.

In our example above, the user who inserts the data is based in Australia which is UTC +10. Our data would look like this:

2021-03-25 10:00:00 +10

It shows the +10 at the end of the time, which is the UTC offset.

We could also store their timezone name:

2021-03-25 10:00:00 +10 Australian Eastern Standard Time

However, there are a couple of issues with this.

• When this user reads the time in the future, they may need to convert it to or from daylight saving time.
• When other users need to read this time, they need to convert to their local timezone
• The data in this column will be in all different timezones, making it hard to sort, filter, and perform other operations.

 

Option 2: Store Server Time

Another option is to store the time in the server’s timezone.

If you’re in Australia, your teammate is in India, and your server is on the east coast of the US, then you could simply store all dates and times in the US Eastern timezone.

The data could look like this:

2021-03-25 10:00:00 -5 US Eastern Standard Time

You’ll need to store the offset and timezone name, because the timezone where the server is could change. The timezone could change to daylight savings time, which means your times in the database will be inconsistent (some will be in standard time, others in daylight savings time).

Or, if you stick to standard time in the database column, you’ll have to convert the data when you insert it.

It could result in inconsistent data.

 

What’s the Solution?

There’s one widely-recommended solution that works for almost all cases:

Store dates in UTC timezone and display them in the user’s timezone.

There are a couple of exceptions to this, which we’ll discuss.

Let’s take a look at the rules for this recommended solution.

 

Rule 1: Store Dates In UTC

The widely-recommended solution for storing dates and times is to store the date and time in UTC.

This means, whenever you have a user that inserts or updates a datetime value in the database, convert it to UTC and store the UTC value in the database column.

Your data will be consistent. All data in this column (and all datetime columns) are stored in the same timezone, which makes working with it much easier.

Any conversions or calculations can then be performed easily on it.

 

Rule 2: Use an Appropriate Data Type

There are many data types available in each database for storing dates and times.

Avoid those that just use a date, as you’ll lose the time.

Avoid using text columns such as VARCHAR as you can’t easily validate them or

What data types should you use for storing dates and timezones?

• Oracle: Use the TIMESTAMP WITH TIME ZONE data type as this can store the timezone of UTC.
• SQL Server: Use the DATETIMEOFFSET data type, which can store the timezone of UTC.
• MySQL: Use the TIMESTAMP data type which can include a timezone component.
• PostgreSQL: Use the TIMESTAMP WITH TIME ZONE data type as this can store the timezone of UTC.

You could store the data in a data type that just stores the date and time, and assume it’s all in UTC. However, it may be easier to perform calculations if the timezone is stored along with it, which is why the above data types include a timezone component.

 

Rule 3: Display in User’s Timezone

The third rule is to display dates and times in the user’s timezone. This is to make it easier for the user to understand the times that things happened.

If the data is stored in UTC, it can be converted to the user’s timezone and displayed to them. This would show different results for users in different timezones.

How do we know what the user’s timezone is? There are two ways to do this.

 

Option 1: User Selects the Timezone

One way to determine the user’s timezone is to have the user select it and store it on their profile.

If you’ve ever signed up to a forum, one of the questions you may have answered on your profile is what your timezone is. This can be used to calculate the times to display on forum posts.

This approach can be taken in your application: ask the user to select their timezone, and save it to their profile in the system. The calculation to display dates and times can use this timezone and the data that is stored in UTC timezone.

If you take this approach, don’t store the timezone offset (e.g. -5, +9). This is because the offset can change, for daylight savings for example. It’s better to store the timezone name.

TImezone names use an IANA timezone identifier (also called an “Olsen name”), which is a string that looks like this: “America/Los Angeles”. This string can be stored to uniquely identify the timezone. There is no unique integer ID for this value, but fortunately, many databases can work with these IANA names.

How long should this field be?

The longest string as of September 2021 is 32 characters: “America/Argentina/ComodRivadavia”.

As a comparison, MySQL has a column called “name” in the mysql.time_zone_name which is 64 characters.

So, you could store this in a VARCHAR(64) column, to cater for any new names in the future.

You may want to store the possible timezones in a lookup table, and give each of them a unique ID, and relate this to your user table.

 

Option 2: Calculate the Timezone

The other option if you don’t want to ask the user to select the timezone is to have the web application calculate it.

This can be done in some languages and can be quite accurate.

You could look up how to do this in the programming language you’re using.

 

Exception: Store Datetimes for Future Events

Earlier in this guide, I mentioned there was an exception to this rule of “store dates in UTC”.

The exception is when you want to store either of:

• Future dates
• Recent past dates

Why are these an exception?

 

Storing Future Dates

Storing dates as UTC values for future dates may pose issues in the future because we don’t know when daylight saving and timezone rules change.

This is described in a blog post by Jon Skeet.

An example of this issue could be:

1. In December 20201, a user schedules an event for 19 April 2025, at 1PM in the US East timezone (UTC -5): 2025-04-19 01:00 PM -5
2. This is converted to UTC and then saved to the database: 2025-04-19 08:00 AM +0
3. At some point in 2023, the US East changes its timezone rules to make its UTC offset from -5 to -6.

This causes an issue with future dates and times. In this example, when 19 April 2025 arrives, the event that was created for 1 PM is saved as 8 AM, but then when it is converted to the user’s timezone it is shown as 2 PM (6 hours are added, instead of the original 5).

The original requester set it for 1 PM but it now shows as 2 PM, which is incorrect. Do we need to change the original time if the timezone changes?

Jon proposes several solutions in his article:

• convert to UTC and use that forever (which can cause issues as shown in this example)
• convert to UTC, and reconvert if rules change (this would mean more metadata about timezones and rules need to be stored)
• preserve local time and use UTC as a derived time (Jon’s preferred option)

Take a look at Jon’s post for more information if you plan on storing a lot of future dates.

If you’re just storing past dates, such as log events or record created times, then this may not be an issue.

 

Storing Recent Past Dates

There is another exception to this rule and that is for events that have happened in the recent past.

This happens when there is a delay between when new timezone rules go into effect and when an application is updated to handle it.

Here’s an example:

1. On 1 July 2021, it’s announced that the US Central timezone will be split into two timezones, one with an offset of -6 and one with -6:30.
2. This rule will come into effect on 1 Sep 2021.
3. Your team finds out about the changes but is unable to deploy an update to your application’s timezone calculation logic until 1 Dec 2021.
4. This means, for the period between 1 Sep 2021 and 1 Dec 2021, your application is using incorrect calculation logic. Dates and times in this impacted timezone are slightly incorrect. Once the deployment occurs, it may include a script to update impacted data.

So, depending on your deployment process and how quickly you can make changes, there may be a period where data would be incorrect when timezones change.

 

Conclusion

Timezones can be tricky to work with in databases as there is a range of things to consider. Generally, storing dates and times in the UTC timezone, and converting to the user’s timezone to display, is the preferred option. Future dates can be an exception to this, but there are solutions that work for that as well.

Did you have any questions or issues using timezones in your database? Let me know in the comments below.