Help Center/ Data Lake Insight/ FAQs/ Flink Jobs/ Flink SQL Jobs/ Why Is the Time Read by a Flink OpenSource SQL Job from the RDS Database Is Different from the RDS Database Time?
Updated on 2024-11-15 GMT+08:00
View PDF
Share

Why Is the Time Read by a Flink OpenSource SQL Job from the RDS Database Is Different from the RDS Database Time?

Symptom

The time read by a Flink OpenSource SQL job from the RDS database is inconsistent with the RDS database time.

Possible Causes

The time zone of a database is improperly set. Generally, there is a 13-hour difference.

Run the following statement in the RDS database:

show variables like '%time_zone%'
The result is as follows:
Figure 1 Execution result
Table 1 Parameters

Parameter

Description

system_time_zone

Database time zone

The value SYSTEM indicates the system time of the database server. The value of the system time zone is CST. Therefore, the database time zone is CST.

time_zone

Time zone of the server where the database is located. The server is a computer.

If the default time zone of the computer where the local database is located is China Standard Time, the value of system_time_zone is CST.

Root cause: A bug can occur when time_zone in MySQL is set to SYSTEM and system_time_zone is set to CST.

In MySQL, CST refers to China Standard Time, which is UTC+08:00. However, in Java, CST stands for Central Standard Time (USA), which is UTC–05:00.

Flink TaskManager is a Java process that reads the time zone set in the JDBC driver code of the MySQL database through TimeZone.getTimeZone(canonicalTimezone). The method gets time zone CST (UTC+8), but the actual time zone is CST (UTC-5).

Solution

  1. Do not set the value of time_zone to SYSTEM. You can set it to, for example, +08:00.
  2. Include the time zone when setting jdbcUrl.

    For example, jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai.

Parent topic: Flink SQL Jobs