Understand datetime types in database

Jun 21, 2022

手贱偶然点开了网易新闻，就看到这个
在中国写代码真的不用考虑国际化啊 pic.twitter.com/THz45NRRIn
— YU (@FreiheitYu) June 16, 2022

One day, I saw this on Twitter: "I accidentally opened NetEase News but to see this. Writing code in China really doesn't need to consider internationalization." this led to my curiosity about how to store and display time when the application needs to take i18n in consideration.

The i18n and l10n are never something to be considered in China since the major language is Chinese and the language of minor ethnicities is seldomly considered. Regarding the timezone, although China geographically crossed over 5 time zones, Beijing Time (BJT), which is UTC+8, is the standard timezone over the nation.

How the time is represented on the computer

To understand this, we first need to know the UNIX timestamp. It is a system to define a point of time by using the number of seconds elapsed since 1970-01-01T00:00:00Z. When saving such timestamp, the timezone info was trimmed and therefore it is not able to know which device in what timezone saved such information. However, the timestamp can be converted to any local time by offsetting the timezone. Below is an example of this:

Date: 2022-06-21T14:46:49+00:00
Timestamp: 1655822809

Local time:
in UTC-5: 2022-06-21T09:46:49-05:00
in UTC+8: 2022-06-21T22:46:49+08:00

How the time is stored in the database

Although the latest standard of SQL is ISO/IEC 9075-2:2016, we will use ISO/IEC 9075-2:1999 in this post since the newer version needs to be purchased.

In the SQL standard, datetime types were defined as follows:

The data types TIME WITHOUT TIME ZONE and TIME WITH TIME ZONE are collectively
referred to as time types (or, for emphasis, as time with or without time zone).
The data types TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE are collectively referred to as timestamp types (or, for emphasis, as timestamp with or without time zone).
The data types DATE, time, and timestamp are collectively referred to as datetime types.
Values of datetime types are referred to as datetimes.

The difference of with or without time zone are described below:

A datetime value, of data type TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIMEZONE, may represent a local time, whereas a datetime value of data type TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE represents UTC. On occasion, UTC is adjusted by the omission of a second or the insertion of a ‘‘leap second’’ in order to maintain synchronization with sidereal time. This implies that sometimes, but very rarely, a particular minute will contain exactly 59, 61, or 62 seconds. Whether an SQL-implementation supports leap seconds, and the consequences of such support for date and interval arithmetic, is implementation-defined

A table was also to be found in the standard:

The MySQL implementation

MySQL uses DATETIME to represent TIMESTAMP without TIMESTAMP. The timezone info will be discarded once it is stored. However, storing the DateTime into fields with TIMESTAMP type will implicitly convert the DateTime into UTC timezone. But neither of these types stores the timezone part of the DateTime.

MySQL :: MySQL 8.0 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types

When retrieving the data, data in DATETIME fields will be displayed in an as-is manner, where data in TIMESTAMP fields will be displayed as the local time of the session connected.

Therefore, MySQL does not store the timezone info. However, when store into the TIMESTAMP fields, the time will be converted.