Original: Coding diary (wechat official ID: Codelogs), welcome to share, reprint please reserve the source.
Introduction to the
As we all know, there are two time types in mysql, timestamp and datetime. However, when searching the difference between timestamp and datetime on the Internet, we will find many completely opposite conclusions related to time zone on the Internet, mainly two kinds:
- Timestamp does not have a time zone problem, while datetime does have a time zone problem. The reason is that timestamp is stored in UTC format, while datetime is stored as a time string
- Timestamp is a timestamp problem in mysql
Two views make people confused, then timestamp is not a time zone problem?
The basic concept
Time zone: Due to geographical limitations, people invented the concept of time zone to adapt to people’s differences in time perception. For example, in China, the time zone is EAST 8, which represents +8:00, or GMT+8, while in Japan, the time zone is East 9, which represents +9:00, or GMT+9. When it is 8 am in China, it is 9 am in Japan. So 8 o ‘clock east 8 and 9 o ‘clock East 9, these two times are the same. In addition, there are two concepts of time:
- Absolute time:
For example, the Unix time fix is the number of seconds from 1970-01-01 00:00:00 to the present, such as 1582416000. This expression is the absolute time and is not affected by the time zone. It is also called epoch epoch.
- Local time:
For example, 2020-02-23 08:00:00 in east 8 is the local time of Chinese people, while at this time, the local time of Japanese people is 2020-02-23 09:00:00, so the local time is related to a certain time zone. If you look at the local time outside the time zone, It doesn’t make sense because you don’t know what point in time you’re referring to.
For example, in Java, the Date object is an absolute time string formatted by SimpleDateFormat in the form of YYYY-MM-DD HH: MM :ss, which is the local time. If SimpleDateFormat does not call setTimeZone() to display the specified time zone, the default is the time zone on the operating system on which the JVM is running, and the time zone on our development machine is almost always GMT+8.
Timestamp is different from datetime
Time_stamp timestamp, date_time datetime, create_timestamp, create_datetime 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`)
)
Copy the code1. First, set the time zone of the database to +8:00, that is, east 8 of China
2. Then manually insert a fixed time as follows and the current time with the now() function
3. After the data is inserted, we change the time zone of the current session to+ 9East 9, Japan, and then look at the data again
4. As above, it is defined astimestampType of columntime_stamp,create_timestampWhether it’s manually inserted ornow()East 9 is an hour longer than east 8. This is correcttimestampThe type is time zone dependent, but is defined asdatetimeThe type ofdate_time,create_datetimeThe field, the time has not changed, this showsdatetimeThe type is time zone independent.
Timestamp contains time zone, datetime does not contain time zone.
Convert 2020-02-23 08:00:00 from east 8 to Unix time fix (absolute time) and insert it into database. Convert the time string to the Unix time fix using the Linux date command as follows:
$ "date" --date="The 2020-02-23 08:00:00 + 08:00" +%s
1582416000
Copy the codeAnd then use mysqlfrom_unixtime()Function to convert the Unix time fix to the mysql time type to insert data.
As above, the queried time is also 9 o ‘clock in the east 9th district, and the time is also correct.
Timestamp type: timestamp
I found that online said timestamp sometimes area problems, are the application end to insert data, and then to the database to look, the results found that the time is not the same, so I am going to write a Demo in Java to try to see if I can reproduce the problem.
Entity (time_test); Entity (time_test); Entity (time_test);
2. Then, I wrote two interfaces/insertwith/queryAllTo insert and query data as follows:
3. Then I set the time zone of the database to+ 09:00The time zone, known as zone 9 East of Japan, is as follows:
4. Then call/insertInterface insert data, notice my interface incoming time is 8 o ‘clock east 8 zone, as follows:
5, after insert, go to the database query, as follows:
It can be seen that the time of time_stamp field is 9 o ‘clock, and I have set the database time zone as east 9 o ‘clock, east 9 o ‘clock and East 8 o ‘clock, the two times are actually equal, so the time data is correct.
6. Then I use/queryAllThe interface queries the data as follows:
timeStampAttributes are1582416000000This is the millisecond time fix and the second time fix1582416000“Corresponds to east 8The 2020-02-23 08:00:00And the time data is also correct!
7, then I change mysql time zone back8 +And restart our Java application as follows:
8. Query the data as follows:
timeStampAttributes or1582416000000Time does not change, and this is also true.
Timestamp has a time zone problem.
After checking, I noticed that they all mentioned JDBCserverTimezoneCould this configuration error be the cause? Try it first!
1. As shown in the figure, I changed the database time zone back+ 9Timezone, and then deliberately configure serverTimezone on the JDBC URL to be inconsistent with the databaseGMT+8Time zone and restart the Java application as follows:
url: jdbc:mysql://localhost:3306/testdb? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8Copy the codeAmong themGMT%2B8isGMT+8Because you need urlencode on the URL, so it becomesGMT%2B8.
2. Insert data again at 8 o ‘clock in the east 8 zone as follows:
3, Then, I go to the database query, as follows:
time_stampIt was 8 o ‘clock in the middle! Note that we inserted 8 o ‘clock in east 8, but the current session is east 9, 8 o ‘clock in east 8 is equal to 9 o ‘clock in east 9, so the correct display should be 9 o ‘clock, the time difference is 1 hour!
4. Then, I call again/queryAllInterface query, want to see mybatis query out of the time data is correct, as follows:
You can seetimeStampis1582416000000, the second level is1582416000It’s 8 o ‘clock east 8, 9 o ‘clock East 9! The query turned out to be correct, why??
The essence of serverTimezone
In order to find out the cause of the problem, I debugged the MYSQL JDBC driver code and finally figured out the cause.
1. Mysql driver after creating a connection, invokes com. Mysql. JDBC. ConnectionImpl# configureTimezone () to configure this connection time zone, if the configuration serverTimezone, The time zone configured for serverTimezone is used, and the time_zone variable is fetched from the database if not configured, which is why the result is correct even if the serverTimezone variable is not configured.
// If a common driver is used, use this method to set the time zone of the mysql connection
com.mysql.jdbc.ConnectionImpl#configureTimezone()
// If the Cj driver is used, use this method to set the time zone of the mysql connection
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone()
Copy the code2. Call the JDBC setTimestamp () method, the actual call is com. The mysql. Cj. JDBC. ClientPreparedStatement# setTimestamp (), there will be according to the specified serverTimezone time zones, Converts the corresponding Timestamp object to the local time string of the specified time zone.
3. When executing SQL statements, executes com. Mysql. Cj. JDBC. ClientPreparedStatement# the execute (), it holds true sendPacket variables will send SQL statements to mysql.
Note: mysql-connector-java is the JDBC url for the mysql-connector-java driver. The mysql-connector-java is the JDBC URL for the mysql-connector-java driver. UseTimezone = true&serverTimezone = GMT % 2 b8 and setTimestamp () is the corresponding com. Mysql. JDBC. PreparedStatement# setTimestampInternal method.
The principle is summarized as follows: Before sending SQL, the mysql driver will convert the Date object parameter in JDBC into a Date string based on the time zone configured by the serverTimeZone, and then send an SQL request to the mysql server. The Date value in the result is also a Date string, which the mysql driver converts to a Date object based on the time zone configured for serverTimeZone.
Therefore, when serverTimeZone is inconsistent with the actual time zone of the database, a time zone conversion error occurs, resulting in time deviation as follows: SQL > select Date, 2020-02-23 08:00:00, 2020-02-23 08:00:00, 2020-02-23 08:00:00, 2020-02-23 08:00:00 I just put it in words: 2020-02-23 08:00:00, East 8. Mysql driver will convert the Date object to 2020-02-23 08:00:00, and then send the SQL to mysql. Date: 2020-02-23 08:00:00; Date: 2020-02-23 08:00:00; Date: 2020-02-23 08:00:00; Mysql > select * from ‘2020-02-23 08:00:00’; mysql > select * from ‘2020-02-23 08:00:00’; At this time the database save time is east 9 2020-02-23 08:00:00, that is, east 8 2020-02-23 07:00:00, save time deviation of 1 hour. D) the time of the east 9 region is the same as the time of the east 8 region.
So, if we change the serverTimezone configuration correctly, i.e. to be consistent with the database, the query time will be wrong, and will be 1 hour less.
1. Use the same EAST 9 GMT+9 in JDBC URL as in database, as follows:
url: jdbc:mysql://localhost:3306/testdb? serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8Copy the codeGMT%2B9 is GMT+9.
2, Restart the Java application, and then query it as follows:
Returns a millisecond time fix1582412400000In seconds1582412400Convert to a time string using the Linux date command:
$ "date" --date="@ 1582412400" +"%F %T %z"The 2020-02-23 07:00:00 + 0800Copy the codeSee? It’s east 8 at 7:00, exactly one hour off.
3. Therefore, when using the mysql timestamp type, for Java applications, it is important to ensure that the serverTimezone in the JDBC URL is consistent with the time zone configuration in the database. If serverTimezone is not configured, the mysql driver will automatically read the time zone configured in mysql Server. As follows:
The mysql driver automatically reads pits in the database time zone
3.1 After mysql is installed, the default time zone isSYSTEMAnd theSYSTEMRefers to thesystem_time_zoneVariable time zone, as follows:
3.2 When the mysql driver reads the time_zone variable isSYSTEM, will read againsystem_time_zoneVariable, andsystem_time_zoneFor domestic, the default isCSTThis is a confusing time zone, which is an abbreviation for 4 different time zones, as follows:
For Linux or MySQL, CST is considered Chinese standard time (+8:00), but For Java, CST is considered US Standard time (-6:00) :
As follows, in Linux, CST is equal to+ 0800, Chinese time zone:
$ "date" +"%F %T %Z %z"
2021-09-12 18:35:49 CST +0800
Copy the codeSo CST in Java is equal to- surf, US time zone:
3.3 Therefore, when the mysql driver obtains the value of CST, it will think it is -6:00, but the mysql driver understands it as +8:00. Therefore, the mysql time zone must be set to a specific time zone, such as +8:00, rather than CST. If the mysql time zone is CST and cannot be modified, the mysql time zone must be set to +8:00. Ensure that the serverTimezone of JDBC is set to a clear time zone (for example, GMT+8).
Entity where the date property is String?
1. We change the time attribute of the Entity object to String (not recommended), as follows:
2. Then write two interfaces as well,/insert2with/queryAll2That is as follows:
Mysql > insert data into SQL server; SQL > insert data into SQL server;
4, then query a handful as follows:
As shown above, the value of the time_stamp field is 8 o ‘clock, but the database time zone at this time is east 9 o ‘clock, so this is east 9 o ‘clock.
5, then I changed the serverTimezone in the database and JDBC to east 8 zone, after the change, restart the Java application, as follows:
url: jdbc:mysql://localhost:3306/testdb? serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8Copy the code6, insert data again, parameter is no time zone 8, as follows:
Mysql > select * from db;
As shown above, the time_stamp field value is 8 o ‘clock, but now the database time is east 8, so this is east 8 o ‘clock.
8. Then I change the serverTimezone on the JDBC URL to east 9 and restart the Java application as follows:
url: jdbc:mysql://localhost:3306/testdb? serverTimezone=GMT%2B9&useUnicode=true&characterEncoding=utf8Copy the codeNow serverTimezone is inconsistent with the database. The database is east zone 8 and serverTimezone is east zone 9.
9. We insert 8 points in the never-time zone again as follows:
10, then query a handful as follows:
The value of time_stamp field is still 8 o ‘clock, the database is east 8 o ‘clock, so this is east 8 o ‘clock, but our serverTimezone is inconsistent with the time zone of the database, there is no time deviation, why?
ServerTimezone converts a Date object from JDBC to a time String in the corresponding time zone before sending it to mysql. So no matter how serverTimezone is configured, it has no effect on the time String.
In this case, it seems that date types in Java are better stored as time strings, but consider that the caller passes a time string at 8 o ‘clock in the infinite zone, and the database takes the liberty of thinking it is 8 o ‘clock in the east 9 zone, but what if the time string is actually 8 o ‘clock in the east 8 zone? At this time if saved to the database for the east 9 area 8 point, that data is wrong!
What if the current API interface transmits a timeless String, Entity is defined as a String, how to solve? 1, ask the interface definition person, this interface time string refers to what time zone, such as east 8 2020-02-23 08:00:00. 2. After the interface receives the time, it needs to convert the time string to the Date object, as follows:
SimpleDateFormat sdf = new SimpleDateFormat('yyyy-MM-dd HH:mm:ss');
sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
Date date = sdf.parse("The 2020-02-23 08:00:00");
Copy the codeIf Entity time attribute is defined as String, format Date object with database time zone as corresponding time String. For example, database time zone is east 9, format 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);
Copy the codeMysql > create Entity in mysql > create Entity in mysql > create Entity in mysql > create Entity in mysql > create Entity
Therefore, using String to store time data is extremely cumbersome to store time values correctly and is not recommended in real development.
Best practices
1. Most teams specify the Unix time fix in the API, because if you pass a time value of 2020-02-23 08:00:00, what time zone is it 8 o ‘clock? For Unix time fixes, this is not a problem because it is absolute time. And if some special reasons, must use the time string, it is best to use the ISO8601 specification of the time string with a time zone, such as: 2020-02-23T08:00:00+08:00.
2, The Entity definition in Mybatis should be consistent with the database definition, the database is timestamp, so the Entity should be defined as Date object, because the mysql driver will automatically convert you to the time zone of the database according to the serverTimezone configuration when executing SQL. If you do the conversion yourself, you will most likely forget to call the setTimeZone() method and use the default time zone of the current Java application machine, which will cause time zone problems if the time zone of the Java application machine is inconsistent with the time zone of the database.
3. The serverTimezone parameter of JDBC must be set correctly. If the parameter is not set, the mysql driver automatically reads the time zone of the mysql Server.
4. If the time zone of the database is changed, the JDBC serverTimezone must also be changed and the Java application must be restarted. If serverTimezone is not configured, the Java application must be restarted because the mysql driver will cache the current database time zone in a Java variable when initializing the connection. It does not change without restarting the Java application.
Timestamp or int in the database to store time?
If you use ints, it doesn’t matter what time zone the database is in, because it stores absolute time. But in some ways, this solution is just push the time zone problem from the database to the application side, time zone problem will appear in the time string is converted to compose of the process, such as a programmer time after the string from the API interface, didn’t consider the time zone, directly into Unix, compose of time, time zone problem may appear. Therefore, for time string parsing without time zone, be sure to ask which time zone is the time and specify it explicitly in the code!
In addition, there are three disadvantages to using int to store time:
- After developers see this field, they can not understand at a glance what time this time fix is about, so they need to convert it, which will be very tedious.
- like
update_timeSuch fields are provided by the databaseDEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPSo that when updating any field,update_timeIt updates automatically, whereas using int stores would require the programmer to reset the field every time the table is updated, which is easy to forget. - Since int is only 4 bytes, it will overflow after 2038. For timestamp, MySQL can change its underlying storage to 8 bytes, which is relatively easy.
The use of timestamp or int is not a fatal problem.
conclusion
Timestamp itself does not have the time zone problem. The time zone problem is caused by the serverTimezone configuration error, mysql using CST confusion time zone, or Entity defining the date as String type.
Content of the past
Hex, Base64,urlencode,urlencode,urlencode,urlencode,urlencode,urlencode