Problems encountered:

An interface for service A to request service B. The interface parameter includes A date parameter time. Time is A timestamp. In Java, it is of type long. As for the time parameter, the A service reads A dateTime field from the library, turns it into A timestamp and requests the B interface.

The B service converts the timestamp into a Date object and formats it as YYYY-MM-DD HH: MM :ss, but finds that the time is 8 hours different from the corresponding time in the database.

Background:

To solve this problem, we need to have some understanding of the concept of time zones and the difference between a timestamp and a formatted string.

Time zone:

Because the earth is from west to east, people in different areas of the sunrise and the moment is not the same, in order to convenient communication, as you said at breakfast time, that is to refer to this time is 9 o ‘clock (though from the point of view of the universe, the 9) is not the same time, the earth is divided into 24 time zones, is the difference between the adjacent two time zones one hour. With the Greenwich Observatory in The United Kingdom as the origin, divided east 12 area and west 12 area, China across five time zones, but these five time zones are using Beijing time, and Beijing is in the east 8 area.

For more information on time zones, see Li Yongle’s popular science video, or one of the following two articles:

Various time zone concepts

Time processing in Java

Timestamp:

  • The timestamp is a number that can be obtained in Java through the getTime method of the Date object. It is of type long and represents the number of milliseconds that have elapsed since 0:00 GMT on January 1, 1970.

  • For a given moment, the time stamp is the same anywhere on the globe.

Time string:

  1. A string with no time zone information, such as 2021-07-25 10:00:00, is not an absolute time and cannot specify a specific time. Imagine yourself standing in the sky above the earth and looking at the earth at one moment. At this time, a student in Beijing sees his watch as 2021-07-25 10:00:00, but in fact, a student in Tokyo sees his watch as 2021-07-25 11:00:00.

  2. A string with time zone information, such as Fri Jul 23 00:26:54 GMT+08:00 2021, where GMT+8:00 indicates 2021-07-23 00:26:54 in the e8, the time string can be located to a certain time, In other time zones, though, this time might not be represented as 2021-07-23 00:26:54.

Time handling in the service link:

Datetime in the database and Date in Java can be simplified as:

  • The database datetime field, which stores a string in the format YYYY-MM-DD HH: MM :ss.
  • The Date object internally stores a long timestamp using the fastTime field

So, at the end of the day, what we need to analyze is how the timestamp and the time string are converted at different nodes.

When time differences are a problem, as in the example we mentioned at the beginning, where do we start?

First we need to know where in our service link we can set the time zone:

  1. You can set the time zone for the database
  2. The time zone can be set for the service itself. If not, the server time zone is taken (this time will affect how the timestamp in the service is converted into a time string).
  3. Service and database connection can set the time zone, if not set, get the database time zone (this time will affect how the dateTime time string in the database is converted to timestamp)

Here is a typical scenario for combing the service link time Settings:

Database time zone is set to the east 8 zone, C service is responsible for writing data, C itself time zone is set to the east 8 zone, and C database connection is also set to the east 8 zone, C writes a time to the database datetime field, such as 2021-07-25 10:00:00, The 10 points represent 10 points in the east 8 (which can be interpreted as relative time relative to the time zone), and its corresponding timestamp (which can be interpreted as absolute time) is 1627178400000.

Datetime: datetime: datetime: datetime: datetime: datetime: datetime: datetime: datetime: datetime: datetime: datetime But because its time zone is GMT, it converts 2021-07-25 10:00:00 as GMT time, and the final timestamp is 1627178400000+8 hours of milliseconds.

A then uses the wrong timestamp to request B’s service, resulting in an 8-hour difference.

Supplementary experiment:

  1. The database connection setting serverTimezone and the service itself JVM parameter -duser. timezone affect the time the service reads from the database

    Experimental design, change the two values respectively, observe the Date object resolved from the datetime field in the database, the results are as follows:

As you can see from the figure above, the resolved timestamps are the same if the time zone Settings are not changed for the database connection to serverTimezone, so serverTimezone reflects the rule of how to convert datetime to milliseconds.

If -duser. timezone is set differently for the same timestamp, then the string in the Date format will be different. If -duser. timezone is set differently for the same timestamp, then the string in the Date format will be different. Equivalent to the time string in the figure without the time zone information. -duser. timezone indicates the rule of how a timestamp in the service is converted to a time string.

  1. Impact of database timezone Settings

In a database, the most common time formats are datetime and timestamp. In addition to the difference in space and range, datetime is displayed independently of the time zone, while timestamp is displayed independently of the time zone.

Datetime is a string of 2021-07-25 10:00:00, which has no time zone information, but timestamp is stored in UTC, which mysql will convert to UTC. The concept of the same time stamp), when displayed in accordance with the database time zone Settings, to show the specific time zone related to the year, month, day, hour, minute, second.

For a database where the initial time zone setting is time_zone=+8:00, let’s create a test table structure as follows:

CREATE TABLE 'test_time' (' id' bigint(20)) NOT NULL AUTO_INCREMENT COMMENT 'test_time ', 'createTime' datetime NOT NULL COMMENT 'createTime ', 'updateTime' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updateTime ', PRIMARY KEY (' id ') ENGINE = InnoDB DEFAULT CHARSET = UTf8MB4 COLLATE = UTF8MB4_unicode_ci COMMENT =' ';Copy the code

CreatTime = datetime; updateTime = timestamp;

Next, we set the time zone of the data to east 9

set time_zone = '+9:00';
Copy the code

We see this record shown below, where creatTIime is unchanged, but updateTime is shown at 21:39:04, as shown in east 9

Conclusions and Recommendations:

1. Correctly set the time zone of your service and the time zone of the database connection

2. When interacting with other services, try not to use the string YYYy-MM-dd HH: MM :ss because you are not sure whether the time zone of other services is the same as that of your own service. It is better to use the absolute time stamp of time: long