Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the time zone problem of timestamp in mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

As we all know, there are two time types in mysql, timestamp and datetime, but when you search for the difference between timestamp and datetime on the Internet, you will find that there are many opposite conclusions related to the time zone, mainly two:

Timestamp does not have a time zone problem, while datetime has a time zone problem because timestamp is stored in UTC format and datetime storage is similar to a time string

Timestamp also has a time zone problem.

The two views are confusing, but will timestamp have a time zone problem after all?

Basic concept

Time zone:

Due to geographical limitations, people invented the concept of time zone, which is used to adapt to people's differences in time perception. For example, the time zone in China is East 8, which means + 8:00, or GMT+8, while the time zone in Japan is East 9, which means + 9:00, or GMT+9. When it is 8 o'clock in the morning in China, it is 9 o'clock in the morning in Japan, that is, 8 o'clock in East 8 and 9 o'clock in East 9, these two times are equal.

In addition, there are two concepts of time:

Absolute time:

Such as unix time suffix, is 1970-01-01 00:00:00 to the present number of seconds, such as: 1582416000, this is the absolute time, independent of the time zone, also known as the Epoch epoch.

Local time:

Relative to a certain time zone, it is the local time. For example, 2020-02-23 08:00:00 in East 8 District is the local time of the Chinese, while at this time, the local time of the Japanese is 2020-02-23 09:00:00, so the local time is all related to a certain time zone. It is meaningless to look at the local time outside the time zone, because you do not know exactly what time point this refers to.

For example, in Java, the Date object is the absolute time, and the time string formatted by SimpleDateFormat in the form of yyyy-MM-dd HH:mm:ss is the local time. If SimpleDateFormat does not call setTimeZone () to display the specified time zone, then the default is the time zone on the operating system in which jvm is running, and the time zone on our development machine is basically GMT+8.

The difference between timestamp and datetime

As follows, I created a table where time_stamp is of type timestamp, date_time is of type datetime, and create_timestamp and create_datetime are types of timestamp and datetime, but they can be generated automatically by the database.

CREATE TABLE `time_ test` (`id` bigint unsigned, `time_ stamp` timestamp, `date_ time` datetime, `create_ timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `create_ datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', PRIMARY KEY (`id`))

1. First, set the database time zone to + 8:00, that is, East 8 in China.

2. Then insert a fixed time data manually as follows, and insert the current time with the now () function

3. After inserting the data, then we modify the time zone of the current session to + 9:00, that is, East 9 of Japan, and then view the data again.

4. As above, whether the columns time_stamp and create_timestamp defined as timestamp type are inserted manually or inserted by now () function, the time of East Zone 9 is one hour longer than that of East Zone 8. This is correct, indicating that timestamp type is time zone dependent, but the time of date_time and create_datetime fields defined as datetime type remains unchanged, which means that datetime type is time zone independent.

Conclusion:

Timestamp contains a time zone in storage, while datetime does not, indicating that the first statement on the Internet is true.

Look at another example.

Shall we convert 2020-02-23 08:00:00 in East 8 to unix timefix (absolute time), and then insert it into the database?

Use the date command of linux to convert the time string to the unix time suffix as follows:

$"date"-date= "2020-02-23 08:00:00 + 08:00" +% s1582416000

Then use the from_unixtime () function of mysql to convert the unix time affix to the mysql time type to insert the data.

As mentioned above, the query time is also 9: 00 in East 9, and the time is also correct.

Why is it said on the Internet that the timestamp type has a time zone problem?

I found that timestamp has a time zone problem on the Internet, and the application side inserts data and then looks at it in the database. As a result, I find that the time is different, so I plan to write a Demo in Java to see if I can reproduce the problem.

1. First of all, the following is the definition of Entity in Java, corresponding to the above time_test table. Note that the time attributes are all defined with the Date type, as follows:

2. Then, I write two interfaces / insert and / queryAll to insert and query data, as follows:

3. Then I set the time zone of the database to + 09: 00:00, that is, East 9 in Japan, as follows:

4. Then call the / insert API to insert data. Note that the time passed by my API is 8: 00 in East 8, as follows:

5. After inserting, go to the database to query, as follows:

As you can see, the time_stamp field time is 9: 00, and I have set the database time zone to 9: 00 in East 9, 9: 00 in East 9, and 8: 00 in East 8, these two times are actually equal, so the time data is correct.

6. Then I use the / queryAll API to query the data, as follows:

The timeStamp attribute is 1582416000000, which is a millisecond timefix of 1582416000 seconds, corresponding to 2020-02-23 08:00:00 in East 8, and the time data is also correct!

7. Then I changed the mysql time zone back to + 8:00 and restarted our java application, as follows:

8. Query the data again, as follows:

It is also true that the timeStamp attribute is still 1582416000000 and the time has not changed.

Then why does the Internet say that timestamp has a time zone problem?

After looking through it, I found that they all mentioned jdbc's serverTimezone. Could it be caused by this configuration error? Just try it first!

1. As shown in the figure, I modify the database time zone back to + 9: 00:00, then deliberately configure serverTimezone on the url of jdbc to a GMT+8 time zone that is inconsistent with the database, and then restart the java application, as follows:

Url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

Where GMT%2B8 is GMT+8, and because you need urlencode on url, it becomes GMT%2B8.

2. Reinsert the data. Note that the insertion time is still 8: 00 in East 8, as follows:

3. Then, I make a query in the database as follows:

The time in time_stamp is 8: 00! You should know that although we are inserting 8 o'clock in East 8 District, the current session is in East 9 District. 8 o'clock in East 8 District is equal to 9 o'clock in East 9 District, so the correct display should be 9 o'clock, and the time difference is one hour!

4. Then, I call the API / queryAll to make a query to see if the time data queried by mybatis is correct, as follows:

You can see that the timeStamp is 1582416000000, and the second is 1582416000. This time is 8 o'clock in East 8 District and 9 o'clock in East 9 District. The query time turned out to be correct, why?

The essence of serverTimezone

In order to find out the problem, I debugged the jdbc driver code of mysql and finally figured out the reason. Here are the main points:

After the 1.mysql driver creates a connection, it will call com.mysql.jdbc.ConnectionImpl#configureTimezone () to configure the time zone of the connection. If serverTimezone is configured, it will use the time zone configured by serverTimezone. If it is not configured, it will fetch the time_zone variable in the database. This is why the result is correct when we do not configure the serverTimezone variable.

/ / if a normal driver is used, use this method to configure the time zone com.mysql.jdbc.ConnectionImpl#configureTimezone () of the mysql connection / / if the cj driver is used, use this method to configure the time zone com.mysql.cj.protocol.a.NativeProtocol#configureTimezone () of the mysql connection

two。 When you call the setTimestamp () method of jdbc, you actually call com.mysql.cj.jdbc.ClientPreparedStatement#setTimestamp (), which converts the corresponding Timestamp object into the local time string of the serverTimezone specified time zone according to the time zone specified by serverTimezone.

3. When the sql statement is executed, com.mysql.cj.jdbc.ClientPreparedStatement#execute () is executed, where the sendPacket variable holds the sql statement that will actually be sent to mysql.

Note: if you look at the source code of mysql-connector-java driver in version 8.0.11, the code varies slightly from version to version, for example, version 5.2.16. You need to configure both configurations on jdbc url: useTimezone=true&serverTimezone=GMT%2B8, and setTimestamp () corresponds to the com.mysql.jdbc.PreparedStatement#setTimestampInternal method.

The principles are summarized as follows:

Before sending sql, the mysql driver will convert the Date object parameters in jdbc into a date string according to the time zone configured by serverTimeZone, and then send the sql request to mysql server. Similarly, after mysql server returns the query result, the date value in the result is also a date string, and the mysql driver will convert the date string into a Date object according to the time zone configured by serverTimeZone.

Therefore, when the serverTimeZone is inconsistent with the actual time zone of the database, a time zone conversion error will occur, resulting in a time deviation, as follows:

A, for example, the sql parameter is a Date object, and the time value is 2020-02-23 08:00:00 in East 8. Note that what is stored in it is not the string 2020-02-23 08:00:00, it is the Date object (absolute time), but I use words to express it is 2020-02-23 08:00:00 in East 8.

B, then, because serverTimeZone is configured with East 8, the mysql driver will convert the Date object to 2020-02-23 08:00:00, notice that it is already a string, and then send sql to mysql, notice that the Date parameter has been replaced with 2020-02-23 08:00:00 in the sql here, because the Date object itself cannot go through the network.

C, then the mysql database receives the time string 2020-02-23 after 08:00:00, because the database time zone configuration is East 9, it will think that this time is East 9, and it will parse the time string by East 9. At this time, the time saved in the database is 2020-02-23 08:00:00 in East 9, that is, 2020-02-23 07:00:00 in East 8, which means a deviation of 1 hour.

D, why is the time in the query result right again, because the query result returns the time string of the East 9 area, and the java application understands it as the time of the East 8 District, which is negative and positive!

Keep serverTimezone in line with mysql time zone

So, then if we change the serverTimezone configuration correctly, that is, when it is consistent with the database, the time that should be queried will be wrong, and it will be less than 1 hour.

1. Use the same East 9 GMT+9 as the database in jdbc url, as follows:

Url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

The GMT%2B9, that is, GMT+9.

2. Then restart the Java application, and query it again, as follows:

The millisecond time suffix 1582412400000 is returned, which means 1582412400 seconds, which is converted to a time string using the date command of linux:

$"date"-date= "@ 1582412400" + "% F% T% z" 2020-02-23 07:00:00 + 0800

See, it's 7: 00 in East 8, exactly one hour short.

3. Therefore, when using the timestamp type of mysql, for java applications, make sure that the serverTimezone in jdbc url is consistent with the time zone configuration in the database.

Another point is that when serverTimezone is not configured, the mysql driver will automatically read the time zone configured in mysql server, which also has holes! As follows:

Mysql driver automatically reads the time zone of the database

3.1 after mysql is installed, the default time zone is SYSTEM, and SYSTEM refers to the time zone of the system_time_zone variable, as follows:

3.2 when the mysql driver reads that the time_zone variable is SYSTEM, it will read the system_time_zone variable again. For China, system_time_zone defaults to CST, which is a chaotic time zone and an acronym for four different time zones, as follows:

For Linux or MySQL, CST is considered to be China Standard time (+ 8:00), but Java thinks CST is US Standard time (- 6:00). (note: it may be related to Java running in Windows):

As follows, CST in linux equals + 0800, that is, China time zone:

$"date" + "% F% T% Z% z" 2021-09-12 18:35:49 CST + 0800

As follows, CST in java equals-06:00, US time zone:

So when the mysql driver takes the time zone value of CST, it will think that it is the-6:00 time zone, but MySQL understands it as + 8: 00:00 zone, so the MySQL time zone must not be configured as CST, but must be configured as a specific time zone, such as + 8:00, but if the MySQL time zone is CST and cannot be modified, be sure to configure the serverTimezone of jdbc for a clear time zone (e.g. GMT+8).

What if the date attribute in Entity is String?

1. We change the time attribute in the Entity object to String (not recommended), as follows:

2. Then write two interfaces, / insert2 and / queryAll2, as follows:

3. Then insert the data. Note that I directly give the 8 o'clock of the no time zone to sql as a parameter, as follows:

4. Then make another query, as follows:

As shown above, the time_stamp field value is 8: 00, but at this time the database time zone is East 9, so this is 8: 00 in East 9.

5. Then I change the serverTimezone in both the database and jdbc to East 8, and restart the Java application after the change, as follows:

Url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8

6. Insert the data again, and the parameter is still 8 points without time zone, as follows:

7. Check again, as follows:

As shown above, the time_stamp field value is 8: 00, but now the database time is East 8, so this is 8: 00 in East 8.

8. Then I adjust the serverTimezone on jdbc url to East 9, and then restart the Java application, as follows:

Url: jdbc:mysql://localhost:3306/testdb?serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8

Now serverTimezone is inconsistent with the database, the database is East 8, serverTimezone is East 9.

9. We re-insert 8 points without time zone, as follows:

10. Then make another query, as follows:

The time_stamp field value is still 8: 00, and the database is East 8, so this is 8: 00 in East 8, but our serverTimezone is not consistent with the time zone of the database. We don't see any time deviation. Why?

Explain

As mentioned earlier, the Date object in jdbc will be converted to the time string of the corresponding time zone according to serverTimezone before sending it to mysql, but now the time attribute in Entity is of String type, and the mysql driver will not convert it, so no matter how serverTimezone is configured, it has no effect on the time string of String type.

In this case, it seems that it is better to use a time string to store the date type in java, and it is not easy to make mistakes, but please seriously consider that the caller passes a 8: 00 point without a time zone, and the database takes it as 8: 00 in East 9, but what if this time string is actually 8: 00 in East 8? At this time, if you save it to the 8 o'clock in the East 9 area in the database, the data will be saved incorrectly!

What if the time series without time zone is passed on the api interface and the String is defined in Entity?

1. Ask the interface definition personnel which time zone the time string of this interface refers to, for example, 2020-02-23 08:00:00 in East 8.

2. After the time is received by the API, the time string will be converted into a Date object in East 8, as shown below:

SimpleDateFormat sdf = new SimpleDateFormat ('yyyy-MM-dd HH:mm:ss'); sdf.setTimeZone (TimeZone.getTimeZone ("GMT+8")); Date date = sdf.parse ("2020-02-23 08:00:00")

3. Then if the time attribute in Entity defines String, then we need to format the Date object into a corresponding time string based on the time zone of the database. For example, if the time zone of the database is East 9, the formatted time string is 2020-02-23 09:00:00, as follows:

SimpleDateFormat sdf = new SimpleDateFormat ('yyyy-MM-dd HH:mm:ss'); sdf.setTimeZone (TimeZone.getTimeZone ("GMT+9")); String dateStr = sdf.format (date); entity.setTimeStamp (dateStr)

4. If you save the Entity to mysql, it will also be 2020-02-23 09:00:00 in East 9, and the result is correct.

Therefore, using the String type to store time data is extremely troublesome to save the time value correctly, and this is not recommended in actual development.

Best practic

1. Most teams will specify that the transfer time in api should be unix, because if you pass a time value of 2020-02-23 08:00:00, which time zone is it exactly 8 o'clock? For unix time affixes, there is no problem because it is absolute time. If for some special reason, be sure to use a time string, it is best to use a time string with a time zone like the ISO8601 specification, such as 2020-02-23T08:00:00+08:00.

2. The definition of Entity in Mybatis should be consistent with the definition of database. If timestamp is defined in database, then Entity should be defined as Date object, because when mysql driver executes sql, it will automatically convert you to the time string of database time zone according to serverTimezone configuration. If you do the conversion yourself, it is very likely that you will use the default time zone of the machine where the current java application is located because you forgot to call the setTimeZone () method. Once the time zone of the machine where the java application is located is inconsistent with that of the database, the time zone problem will occur.

3. The serverTimezone parameter of jdbc should be configured correctly. When it is not configured, the mysql driver will automatically read the time zone of mysql server. At this time, be sure to specify the time zone of mysql server as a clear time zone (e.g. + 08:00), and do not use CST.

4. If the serverTimezone of jdbc is also modified after the database time zone is modified, and restart the Java application, even if serverTimezone is not configured, it also needs to be restarted, because when the mysql driver initializes the connection, the current database time zone will be cached into a java variable, and it will not change if the Java application is not restarted.

Use timestamp or int to store time in the database?

If you use int time affix storage, no matter what the database time zone is, because the storage is absolute time, it seems to solve the time zone problem perfectly.

But from some point of view, this solution only pushes the time zone problem from the database side to the application side, and the time zone problem will occur in the process of converting the time string into a time affix. for example, after a programmer gets the time string from the api interface, does not consider the time zone and directly changes to the unix time affix, the time zone problem may occur.

Therefore, for time string parsing without a time zone, be sure to ask which time zone it is and specify it explicitly in the code!

In addition, there are three other disadvantages of using int to store time:

When developers see this field, they can't get a clear idea of what the time suffix is, and it will be tedious to convert it.

For fields like update_time, the database provides a DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP mechanism so that update_time updates automatically when any field is updated, but if you use int storage, programmers need to re-set this field every time they update the table, which is easy to forget.

Because int has only 4 bytes, using it to store time will overflow after 2038, while for timestamp, it is relatively easy for MySQL to uniformly modify its underlying storage to 8 bytes.

Of course, it is not recommended not to use int, it is a matter of opinion, no matter whether it is timestamp or int, there is no fatal problem.

This is the end of the content of "how to solve the time zone problem of timestamp in mysql". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.