Understand datetime types in database

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.

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.

The PostgreSQL implementation

PostgreSQL supports a full set of datetime types in the SQL specification.

The MS SQL Server implementation

The datetime2 format is roughly equal to TIMEZONE and the datetimeoffset format is roughly equal to DATETIME WITH TIMEZONE.

The SQLite implementation

SQLite does not provide a native data type to store DateTime. Instead, it provides several built-in functions for the DateTime manipulation.