The processing of date and time has always been a problem that seems simple in computer systems, but in fact it often explodes. Stackoverflow: Daylight saving time and time zone best practices - Technical implementation suggestions.

Too long; didn't read:

. In most applications, you only need to use the "absolute time DateTime" technology to implement

. The backend should use UTC time (including DB drop-off and interface definition) uniformly, and should not be affected by the user time zone or server time zone

. The time input and display of the front-end is adjusted according to the specific business scenario, and the accuracy adjustment of

. In the face of dates without time, it is necessary to clearly distinguish between "anniversary day" and "absolute time with low precision". Most of the time you see is the latter, and it should also use "DateTime for determining the time zone" to implement 

1. Importance The processing of date and time in

has always been a problem that seems simple in computer systems, but in fact it often explodes.

For example, every few years, various variants of the " thousand-year-old insect problem" are exposed. Usually, the system did not design a date and time data storage method at the beginning of design, or underestimated the life cycle of the product design, resulting in insufficient data structures of the initial selection.

Millennium Worm Problem:

Older programmers know the Millennium Worm Problem. Before 2000, many systems used 2-digit numbers to represent years, so 99 years was the largest value it could express. Therefore, the year after 1999 is undefined in these systems, and there may even be many strange situations such as "1900", "1:00", "19:0" (why? Interested readers can speculate for themselves).

If the "millennial insect" is caused by the lack of forward-looking design in the time dimension, then another problem that lacks forward-looking is the spatial dimension, that is, the problems brought about by product globalization and cross-time zones. In the globalized products of

, if time processing does not follow unified standards, the entire system will be filled with time conversions that are difficult to understand and maintain. The docking documents of various interfaces have to clearly state "What time zone is the time of this interface? How to deal with it?" If the backend service needs to be deployed across countries in computer rooms on multiple continents, it will require a lot of transformations because the server's time zones are different.

Unfortunately, in most cases, the product will not have the "globalization" attribute from the beginning. So at the beginning, the production and research team will not pay attention to global design issues, and it is easy to leave behind a lack of forward-looking design issues.

Usually, we don’t encourage “over-design”. However, the design of date and time is the least afraid of "over-excessive". At this time, because it is not expensive to implement a forward-looking time and date plan in technology; but if the initial design is not enough, the later upgrade and data migration of work will be a damaging problem.

2. How to express time and date?

2.1 Time and date delivery: Use string

between microservice , and between the front and back ends. It is recommended to use string to pass date and time. Strings are clear and easy to read, easy to debug manually, and the overhead is usually completely acceptable. (An interface with a large amount of time data is recommended to consider using Unix TIMESTAMP.) If you use a string, don't invent the format yourself. There is a very clear international standard: ISO 8601 (wikipedia: https://en.wikipedia.org/wiki/ISO_8601)

The following example is a common format that complies with the specifications:

• Date only: 2022-02-09
• UTC Date time: 2022-02-09T12:36:42Z
• specific time zone date time: 2022-02-09T20:36:42+08:00
• Higher precision time: 2022-02-09T12:36:42.123456789Z

Note that the string format used in MySQL (such as 2022-02-09 12:36:42) does not comply with the specifications and is not recommended.

2.2 Time and date storage: Pay attention to the processing of DateTime

in MySQL in different databases has a lot of differences in time and date related objects.Let’s talk about MySQL here because there are quite a few pitfalls.

MySQL's DateTime data is stored does not contain time zone information , so no time zone conversion is done when reading.

At the same time, every MySQL connection session has the concept of "session time zone", but this concept only affects the behavior of functions such as MySQL's NOW() and has no effect on the DateTime that has been saved in the data.

For example:

SET time_zone ='+00:00' ;UPDATE tab SET datetime_colume = '2020-01-01 00:00:00';SET time_zone ='+08:00' ;-- Change the session time zone SELECT datetime_colume FROM tab;- The return value is still '2020-01-01 00:00:00', which is consistent with the written data and has nothing to do with the session time--------SET time_zone ='+00:00' ;SELECT NOW(); -- Assume that the return '2022-01-01 00:00:00'UPDATE tab SET datetime_colume = NOW(); -- The deposit is '2022-01-01 00:00:00'SET time_zone ='+08:00' ;-- Change the session time zone SELECT NOW(); -- '2022-01-01 08:00:00' has changed according to the time zone SELECT datetime_colume FROM tab; -- '2022-01-01 00:00:00' What has been written will not change to 

2.3 Time and date calculation: language native DateTime Type

Each language generally provides native DateTime data types to express absolute date and time, and all support the parsing and formatting of the above ISO 8601 specification. When

handles relative time zones, various languages ​​usually use the operating system's time zone database to convert it into an absolute time zone. The time zone database needs to be updated regularly by the operating system when connected to the network.

2.4 Unix TimESThamp

Unix Timestamp can be used in storage, calculation and delivery links, which is versatile. It is not suitable to express the date of commemoration.

It represents the difference between an absolute time and Unix Epoch time (defined as 1970-01-01T00:00:00Z) seconds by a value. Unix Timestamp itself has expressed absolute time and does not require time zone information.

When using Unix Timestamp, special attention should be paid to choosing appropriate numerical types, which will affect the range of time representation. If you are not careful, you may plant a new millennium bug.

• uses signed int32, which is represented by at most until 2038. MySQL's TIMESTAMP type is also it. A millennium bug variant
• is signed int64 and uses 9-bit 10 66 fixed-point decimal places, it is Golang's UnixNano(), which can represent from 1678 to 2262
• generally does not use floating point number because the accuracy of floating point numbers is not fixed

3. Date and time design from product perspective

Based on the principle of no importance or omission, we can divide all date and time objects in the product according to the following table:

The following explains these five scenarios one by one.

3.1 represents the world's only determined time point (② in the table)

information volume includes "year, month, day, hour, minute, second, time zone". In this way, an unambiguous time point in the long river of history can be completely determined. This time point is completely objective, has nothing to do with the geographical location of the user being visited, has nothing to do with the geographical location of the server, and has nothing to do with anything to do with it. In terms of product performance, the time display is usually readjusted according to the time zone where the viewer is located.

Use Example:

• The time of a single event occurred. For example, the opening ceremony of the 2022 Winter Olympics : February 4, 2022, 20:00, +0800 time zone. When a British man watches a TV broadcast trailer, he will see that the opening ceremony will be broadcasted at: 12:00 noon on February 4, 2022. This reflects the conversion of time based on viewers.

3.2 represents the local determined time point (① in the table)

contains "year, month, day, hour, minute and second". Because there is no time zone information, it cannot determine an exact time point itself, but it is only meaningful in a specific situation.

The so-called specific situation is because the business scenario contains time zone information and is a recognized consensus. So essentially it still represents an absolute time. In terms of product performance, due to the consensus on time zones, it is not necessary to adjust the display of time based on the viewer's time zone.

Example of use:

• In non-international products, it clearly knows the time zone where the user is located. Then removing the time zone is the easiest way to deal with it, which can save a lot of trouble.
• has other conventional understandings about time zones. For example: the take-off and landing time of the plane and the check-in and departure time of the hotel must be expressed according to the take-off and landing of the plane and the local time zone of the hotel. On all booking websites, the time will be displayed according to this rule, regardless of the time zone the visiting user is in.

3.3 represents the repetitive time (③ and ④ in the table)

compared with the first two categories, the "date" information is removed to describe the repetitive schedule. It can indicate the time zone or not, but based on people's consensus on the time zone.

Use Example:

• holds a meeting every Wednesday at 8:00+0800. If this can be a cross-border meeting, everyone can understand the right time. At this time, you should pay attention to adjusting the display according to the viewer in product performance.
• every Wednesday at 8:00, the time zone of the flight departure point is the consensus contained. There is no need to adjust the display according to the viewer's time zone in product performance.

3.4 Anniversary Date (⑤ in the table)

date object has almost only one meaningful purpose: representing anniversary/festival. It does not contain time zone information.

believes that "date" can only be used for "anniversary", which is a bit absolute. But I did look up a lot of information and didn't see any dates that were not used for "anniversary".

For example:

• Xiao Wu’s birthday is March 11, so whether he is in China or the United States, he will celebrate his birthday on March 11th.
• December 25 is the western Christmas , and all countries celebrate on December 25th, although they are not in the same time zone.

product reflects that the display of dates does not need to be adjusted according to the time zone. In essence, the logic of "anniversary" is actually a habit caused by the lack of rigor in the human brain, and a habit of imprigor and unobjective. It does not include time zone information, just to satisfy this impercise habit.

3.5 Differentiate between "commemorative date" and "absolute time with low precision"

as mentioned above, the date object cannot contain a time zone.You may ask, what about me saying "March 22, 2022 Beijing time"? The answer is: this is not a date, but an "absolute time with low precision".

In many cases, when you want to use dates, what you may actually need is an "absolute time with low precision". This kind of scenario is often encountered in the business of Feishu human resources suite.

For example, a classmate in the United States and a classmate in Japan both resigned from the company on March 22, 2022, and the same HR in Beijing handled the resignation matters.

is visible. From our user's perspective, we actually ignore the accuracy of time. Before the globalization of the product, we ignored the issue of time accuracy through some default simplifications (such as filling in the time as 00:00:00). Once you face the globalization of your products, you need to make up for the time and improve the accuracy. The way to make up for time and improve accuracy needs to be considered and clearly defined according to the specific product form.

For example, in the above-mentioned resignation scenario, it is necessary to supplement it according to the company's definition of resignation, which can be 23:59:59 on the same day local time, or the working hours that day, such as 17:00:00.

For example, for cross-team business, such as a classmate's superior reporting line transfers from an American leader to a Japanese leader, in order to avoid ambiguity, a certain effective time zone is usually agreed, such as the time of the company's headquarters is calculated uniformly.

4. Datetime technology implementation

4.1 DateTime

is suitable for the above four scenarios: ①②③④.

all time objects in the interface exposed by the backend are expressed as UTC time.

At the same time, all backends also use UTC time when storing, calculating, and transmitting time. Since time zone information will be discarded when DB stores time, the lost time zone should be guaranteed, which is clearly agreed upon by everyone, that is, UTC. This way, all time fields in DB are not ambiguous. The time generated within the

interface, such as CreatedAt and UpdatedAt times, should be converted to UTC and then dropped into disk. If you use MySQL's NOW() function directly, you should ensure that the time zone setting of MySQL Session is correct.

is responsible for processing the time entered by the user and the time displayed to the customer on the front end or BFF. Includes two steps:

1. deals with the problem of "low precision time". For example: The effective time of employee movement, the user only sets the accuracy of "day". Then if you are not cross-border, you can complete the 00:00:00 of the user session time zone as the exact entry time; if you are cross-border, it depends on how the customer defines it and how flexibility the product gives the customer: for example, you can use 00:00:00 of the time zone where the customer's company headquarters is located as the exact entry time.
2. time zone conversion. Note that this does not necessarily mean using the user's session time zone to convert. As mentioned above, the reservation time of the airplane, train hotel must be converted by the reservation local time zone.

The above two points must be clearly defined in product design, and you must not be ambiguous.

Don't be serious about :

Due to historical reasons, the DB has been saved in Beijing time, so we can agree that the +0800 time zone is the time for all our backend interfaces. As long as you use a certain absolute time zone, there will be no ambiguity and there is no need to force UTC.

can also process time conversion at the gateway layer of the backend interface. Don't be serious or not, does that count as BFF? What we need is that the time zone conversion logic should be strictly prohibited from going deep into the lower layer of the backend.

4.2 Date

without time zone is suitable for the above ⑤, that is, the anniversary scene.When input or displaying

, no processing is done to the date. The date object is saved directly in the DB.

Only real anniversary is necessary to use this method, and you should be very cautious. For example, save a contact's birthday.

5. Special treatment about time zone

5.1 Uncertainty in time zone

uses absolute time difference to represent the time zone, for example: "East 8th District" means a time zone 8 hours earlier than World Coordinated Time (UTC). This is an objective time zone.

Many times, we focus on the time zone of a city or region. For example: Asia/Shanghai represents Chinese time; the three-letter abbreviation EST represents US Eastern Standard Time. Note that the time difference in these time zones defined by geographical location will change, and factors such as: they may be affected by local policies, or daylight saving time, .

For historical time, the geographic time zone can determine the objective time zone, because no one will redefine the time that has passed.

For future time, the geographic time zone cannot determine the objective time zone. Therefore, if a future event is agreed upon by a non-absolute time zone, it is likely to change. And, Our products need to take into account handling of this change.

For example, if Chinese employees initiate a "8 am every day" cross-national meeting, then in the United States, due to changes in summer time, the time for meetings in winter is different from that in summer. On the contrary, a "8 a.m. every day" multinational meeting initiated by American employees is also different for Chinese employees due to changes in the U.S. summer and winter.

5.2 Daylight saving time

In some countries, they will adjust the time by one hour (one hour ahead of time) in summer. This is manifested in the same area, in winter and summer, using different absolute time zones.

do this because the summer is very long during the day, and after adjustment, you will go to work earlier during the day, so that you will have longer time to dawn after get off work. Note that it is not to adjust the work at 10 o'clock to 9 o'clock, but to redefine the whole society to 10 o'clock one hour in advance.

A specific example is in the United States:

After 1:59:59 am on March 14, 2021, the next second is 3:00:00 am. Therefore, the United States does not actually have on March 14, 2021 at 2:10:00 am. For compatibility, according to RFC5545, if the schedule is around this time when it does not exist, it will be considered 3:10:00.

After 1:59:59 am on November 7, 2021, the next second is 1:00:00 am. Therefore, the United States will actually appear twice at 1:10:00 am on November 7, 2021 at this time . In order to avoid ambiguity, according to RFC5545, when you see this time, you will consider it to be a forward point in time. Therefore, unless you use the time zone of another country to make a date, it is impossible for an American boss to ask you to have a meeting within the second hour of overlap.

Read more and references

• Wikipedia: ISO8601 - Standard

https://en.wikipedia.org/wiki/ISO_8601

• HRT3339 - General suggestions on the implementation of time and date on the Internet

https://www.rfc-editor.org/rfc/rfc3339

• RFC5545 - iCalendar Specifications for Internet calendar applications

https://datatracker.ietf.org/doc/html/rfc5545

• Stackoverflow: Daylight saving time and time zone best practices [closed] - Technical implementation suggestions

https://stackoverflow.com/questions/2532729/daylight-saving-time-and-time-zone-best-practices

• Stackoverflow: How to store repeating dates keeping in mind daylight saving time - Technical implementation suggestions

https://medium.com/@vivekmadurai/how-to-deal-with-date-and-time-cross-time-zones-39b1bd747f35

• Medium: How to Deal with Date and Time across Time Zones - Technical implementation suggestions

https://medium.com/@vivekmadurai/how-to-deal-with-date-and-time-cross-time-zones-39b1bd747f35

• Microsoft365: Behavior and format options of the Date and Time field - Documentation of the time and date fields of Microsoft

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/behavior-format-date-time-field?view=op-9-1

• Time Change 2021 in the United States - United States 2021 How to adjust the 2020-year summer time

https://www.timeanddate.com/time/change/usa?year=2021