百度智能云

All Product Document

          Relational Database Service

          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:

          image.png

          • Connect via MySQL client. The viewed time data is as follows:

          image.png

          • Access the database using JDBC. The viewed time data is as follows:

          image.png

          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 Set time_zone and read data 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.

          Previous
          Replication Delay Resulted from the MySQL Table Without a Primary Key
          Next
          Detailed Interpretations for REFERENCES-command-denied-to-user Error