All About Time Zones - Understanding UTC

Introduction 🙋‍♀️

When developing backend systems, as part of designing tables for a service, we often store data such as the time of creation, modification, and deletion—whether for display on the screen or for logging purposes. In such cases, this time data is stored in a DateTime-type column.
DateTime columns incorporate the concept of a time zone. I’ve habitually set the timezone value of DateTime to True, but in reality, I didn’t fully understand the concept. Therefore, in this post, I’d like to clarify my understanding of time zones and summarize how they should be used in actual services.
Let’s take a quick look at some sample code and dive into the details of time zones☝️

Discover the Meaning of timezone=True!

When defining a table containing time-related columns as an ORM model—especially when using SQLAlchemy—you can write code like this:

python
from sqlalchemy import create_engine, Column, Integer, String, DateTime, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func

engine = create_engine('sqlite:///example.db', echo=True)

Base = declarative_base()

class MyTable(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

Looking at the example code above, you’ll notice that the column created_at is defined as a DateTime. In this case, you can set it to timezone=True.(The default is False.)

Timezone?🤔

Let’s start by clarifying the concept!

So, what does “timezone” mean here? According to Wikipedia, it is defined as follows.

plain
시간대(時間帶,time zone)는 영국의 그리니치 천문대(본초 자오선, 경도 0도)를 기준으로 지역에 따른 시간의 차이,
다시 말해 지구의 자전에 따른 지역 사이에 생기는 낮과 밤의 차이를 인위적으로 조정하기 위해 고안된 시간의 구분선을
일컫는다. 그리니치 천문대를 기준으로 세계의 0시가 결정된다.

As everyone knows, the Earth is round, and due to this round shape, different countries and regions have different time zones. This means that even at the same moment, the time is expressed differently in each country. (This is what causes time differences.)
There is an international standard for denoting these time zones, and the most widely known one is UTC. As explained on Wikipedia, it refers to a standard time zone based on the Greenwich Observatory in the United Kingdom, with each region having a one-hour time difference relative to it.

Let’s take Korean time as an example. If we express the time in each region at the same moment, it can be summarized in the following table.

Korean Time	Greenwich Observatory
2024-02-01 00:00:00	2024-01-31 15:00:00
2024-02-01 09:00:00	2024-02-01 00:00:00

Korea is always 9 hours ahead of Greenwich Mean Time. Therefore, when expressing Korean time in international standard notation, it is denoted as UTC+9. (For a detailed explanation, see the Korean Standard Time (KST) article.)

How is it stored in the database?

Database used: PostgreSQL 16.1

I created a table corresponding to the sample code in the introduction and inserted some arbitrary data.

sql
insert into my_table ("name") values ('test')

Interestingly, the phrase +0900 was included as well. The reason is that the database’s current timezone setting is ‘Asia/Seoul,’ meaning it follows the Korean time zone; therefore, the actual data was stored in Korean time (aligned with UTC+9).

sql
-- 현재 database에서 설정된 timezone 확인 방법
show timezone;

When you select this data using the following SQLAlchemy code, it is retrieved in actual UTC time.

python
from sqlalchemy import create_engine, inspect, Table, text
from sqlalchemy.orm import sessionmaker

engine = create_engine('postgresql+psycopg2://postgres:1234@localhost:5432/postgres', echo=True)

Session = sessionmaker(bind=engine)
session = Session()

res = session.execute(text("select * from my_table"))

print(res.fetchone().created_at)

bash
> 2024-01-31 15:00:00+00:00  // 데이터는 한국 시간으로 저장되었지만, 실제 UTC 시간으로 print 되었다.

Why is UTC used?

As mentioned above, it is because UTC is the “standard” itself. If you operate a service across regions with different time zones, the data displayed on the screen must be presented according to each region’s time zone. This means you must be able to determine where and when the data was stored.
The following example illustrates why a standard is important.

plain
1. 한국에 사는 유진이(UTC+9 시간대 사용)와 뉴욕에 사는 Smith(UTC-5 시간대 사용)는 펜팔을 하고 있다.
2. 유진이는 한국 시간 2024년 2월 1일 14시에 Smith에게 메일을 보냈다.
  - 이는 UTC 시간 2024년 2월 1일 5시에 해당하는 시점이다. (한국 시각 - 9시간)
  - 뉴욕에서는 이 시점의 시간이 2024년 2월 1일 0시다. (UTC 시간 - 5시간)
3. 따라서 Smith는 뉴욕 시간 2월 1일 0시에 해당 메일을 받았다.

• The time in each region at the moment the email was sent, as shown above

UTC	Korea Time	New York Time
2024-02-01 05:00:00+0000	2024-02-01 14:00:00+0900	2024-02-01 00:00:00-0500

• Assuming the time the email was sent from Korea was stored as a DateTime value in the database, it would have been saved as 2024-02-01 14:00:00 +0900. Nevertheless, according to the 'standard,' that DateTime value can be interpreted as 2024-02-01 00:00:00 -0500 in New York.
• When implementing this on the web,
  • On the backend, it is recommended to convert data retrieved from the database to UTC before sending the response, and
  • On the frontend, it is recommended to convert the data to the time zone of the user’s location.

Conclusion⏰

If you’re designing database tables for a service that targets only the domestic market, operates in any country, or runs on a closed network, there’s little reason to give time zones much consideration. However, if there is a possibility that the service will globalize in the future, or if you plan to attract customers from multiple regions (or countries), I believe it is necessary to configure time zones for DateTime management.

References

• Wikipedia: Time zone
• Ramos - Handling DB Time Zones #Recommended Solutions