Detailed Interpretations for the Use of time_zone Parameters
Background
"time_zone" parameter is a variable for MySQL to record the time zone information, and customers frequently launch the need for modifying time_zone, with reasons including but not limited to:
- As for RDS instance that is newly migrated to the cloud, modify "time_zone" to align to the original database instance's time zone information.
- Function in the JDBC time zone brings about the difference of several hours between the time viewed via applications and the actual time.
Failure Phenomenon
See the case below: there is a time gap (13 hours) between the time of timestamp field stored in MySQL and the time viewed in routine access:
- RDS instance's parameter setting is as follows:
- Connect via MySQL client. The viewed time data is as follows:
- Access the database using JDBC. The viewed time data is as follows:
Analysis of cause
Currently, the database instance's time zone parameter "time_zone" value is set as "system", indicating inheritance to the value CST of the system time zone parameter "system_time_zone". JDBC function in charge of time zone configuration has confusion in parsing CST time zone, so the applications get the Cuba standard time:
- CST (China Standard Time), UTC +08:00
- CST (Cuba Standard Time), UTC -05:00
Solutions
Set "time_zone" parameter, and specify the database instance's time zone, without using the CST time zone that causes misunderstanding.
Case review
Create a test table
id int(11) NOT NULL AUTO_INCREMENT,
dtime datetime NOT NULL,
stamp timestamp NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
Test process
Set time_zone and insert data td> | Set time_zone and read data td> | Output Result | datetimeTime Delay of Field | timestamp Time Delay of Field |
set session time_zone='system'; insert into tb_01(id,dtime,stamp) values(1,'2018-12-24 12:00:00','2018-12-24 12:00:00'); |
set session time_zone='system'; | id=1 dtime='2018-12-24 12:00:00' stamp='2018-12-24 12:00:00' |
Invariant | Invariant |
set session time_zone='+8:00'; | id=1 dtime='2018-12-24 12:00:00' stamp='2018-12-24 12:00:00' |
Invariant | Invariant | |
set session time_zone='-6:00'; | id=1 dtime='2018-12-24 12:00:00' stamp='2018-12-23 22:00:00' |
Invariant | 14 hours difference | |
set session time_zone='-5:00'; | id=1 dtime='2018-12-24 12:00:00' stamp='2018-12-23 23:00:00' |
Invariant | 13 hours difference | |
set session time_zone='+9:00'; | id=1 dtime='2018-12-24 12:00:00' stamp='2018-12-24 13:00:00' |
Invariant | 1 hours difference | |
set session time_zone='-6:00'; insert into tb_01(id,dtime,stamp) values(2,'2018-12-24 12:00:00','2018-12-24 12:00:00'); |
set session time_zone='system'; | id=2 dtime='2018-12-24 12:00:00' stamp='2018-12-25 02:00:00' |
Invariant | 14 hours difference |
set session time_zone='+8:00'; | id=2 dtime='2018-12-24 12:00:00' stamp='2018-12-25 02:00:00' |
Invariant | 14 hours difference | |
set session time_zone='-6:00'; | id=2 dtime='2018-12-24 12:00:00' stamp='2018-12-24 12:00:00' |
Invariant | Invariant | |
set session time_zone='-5:00'; | id=2 dtime='2018-12-24 12:00:00' stamp='2018-12-24 13:00:00' |
Invariant | 1 hours difference | |
set session time_zone='+9:00'; | id=2 dtime='2018-12-24 12:00:00' stamp='2018-12-25 03:00:00' |
Invariant | 15 hours difference |
Conclusions & suggestions
Conclusions
- When inserting and reading data, make sure the values of "time_zone" parameters are aligned, or the "timestamp" field might bring about time zone difference, but "datetime" field is free from the influence.
- After the data is inserted into database, "timestamp" field's data content keeps unchanged (checksum table results are consistent) no matter how is time_zone parameter setting adjusted. Only "There exists time zone offset" is shown in reading.
- It is because, upon writing, MySQL converts the timestamp field according to current time zone setting for storage. Upon reading, it converts the timestamp field according to the current time zone setting for display.
Recommendation
Recommend you to modify the time_zone parameter when creating the RDS instance. During usage of RDS instance, you should not make modification or insert and read timestamp data might be mixed due to inconsistency of the time zone settings.