5.1.13 MySQL Server Time Zone Support

11.2 Date and Time Data Types

Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

格林威治时间

UTC

廖雪峰-如何正确的处理时间

jdbc url timezone

Server SQL Modes

ALLOW_INVALID_DATES

STRICT_MODE

NO_ZERO_DATE

  • TIMESTAMP 类型的的范围 1970-01-01 00:00:01.000000 UTC 到 2038-01-19 03:14:07.999999 UTC，注意是 UTC 时间

  • TIMESTAMP 的值存储为自 epoch 1970-01-01 00:00:00 UTC 的秒数

  • TIMESTAMP[(fsp)] fsp (0-6) 表示可以指定小数秒数精度， 默认是 0 ，表示没有用小数部分， 如 ‘2038-01-19 03:14:07.999999’

  • STRICT_MODE, NO_ZERO_DATE 决定是否可以设为 ‘0’ 值 （0000-00-00 00:00:00）

  • Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses. By default, the first TIMESTAMP column has these properties, as previously noted. However, any TIMESTAMP column in a table can be defined to have these properties.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone settings remains constant, you get back the same value you store. If you store a TIMESTAM value, and then change the time zone and retrieve the value, the value is different from the value you stored. The current time zone is avaliable as the value of the time_zone system variable. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.13, “MySQL Server Time Zone Support”.

MySQL Server Time Zone Support

  • system time zone 系统时区，server 启动时，尝试自动获取所在机器的时区，并设置为 system_time_zone 系统变量（system variable）

    要显示指定 Mysql Server 启动时的系统时区，启动 mysqld 服务前，设置 TZ 环境变量。 如果使用 mysqld_safe, 使用 --timezone 选项也可以设置

  • server time zone 服务器当前时区， 全局 time_zone系统变量表明了 server 当前操作使用的时区. time_zone 的初始化值是 SYSTEM, 表示和系统时区 (system time zone) 相同; 初始的全局 server time zone 也可以通过命令行选项 --default-time-zone 来设置，或者在 option 文件中指定 default-time-zone='timezone'； 如果拥有权限，也可以在运行时设置：

1
SET GLOBAL time_zone = timezone;
  • per-session time zone 针对每个会话的时区，每一个连接的客户端都有自己的会话时区设置，通过会话范围内的 time_zone 变量来设置；默认会话变量会从全局 time_zone 变量继承，但是客户端也可以修改：
1
SET time_zone = timezone;

会话时区设置会影响与时区相关（zone-sensitive）的数据类型的展示和存储. 包含 NOW() 或者 CURTIME 函数值的展示，以及 TIMESTAMP 列的值的存储和查询. 存储时，TIMESTAMP 列的值会从当前会话时区转为 UTC 进行存储，查询时，再从 UTC 时间转为当前会话的时区时间

UTC_TIMESTAMP 函数和 DATE, TIME 或者 DATETIME 列的值不受会话时区影响，它们也不是转为 UTC 存储

timezone 时区的设置格式

  • 指定为 SYSTEM, 表明使用系统时区 system time zone
  • 指定一个 UTC 偏移量的字符串, +/-[H]H:MM, 如 ‘+10:00’， ‘-6:00’ （前面会自动补 0）. 有效范围 ‘-12:59’ - ‘+13:00’ inclusive
  • ‘Europe/Helsinki’, ‘US/Eastern’ 类似于这种命名时区，需要 mysql 库中创建的了时区信息表，并且填充了数据

JDBC 查询 TIMESTAMP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// step 1 
com.mysql.jdbc.ResultSetImpl#getTimestamp(int)

// step 2
com.mysql.jdbc.ResultSetImpl#getTimestampInternal

// step3
com.mysql.jdbc.ByteArrayRow#getTimestampFast

// step4
com.mysql.jdbc.ResultSetRow#getTimestampFast(int, byte[], int, int, java.util.Calendar, java.util.TimeZone, boolean, com.mysql.jdbc.MySQLConnection, com.mysql.jdbc.ResultSetImpl)   

// step5
(useLegacyDatetimeCode=false) mysql.jdbc.TimeUtil#fastTimestampCreate(java.util.TimeZone, int, int, int, int, int, int, int)
    或
(useLegacyDatetimeCode=false) com.mysql.jdbc.TimeUtil#changeTimezone(com.mysql.jdbc.MySQLConnection, java.util.Calendar, java.util.Calendar, java.sql.Timestamp, java.util.TimeZone, java.util.TimeZone, boolean)

INIT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
"net_buffer_length" -> "16384"
"interactive_timeout" -> "28800"
"query_cache_size" -> "16777216"
"character_set_connection" -> "utf8"
"max_allowed_packet" -> "16777216"
"net_write_timeout" -> "60"
"lower_case_table_names" -> "0"
"system_time_zone" -> "CST"
"tx_isolation" -> "REPEATABLE-READ"
"time_zone" -> "SYSTEM"
"wait_timeout" -> "28800"
"character_set_server" -> "utf8"
"auto_increment_increment" -> "1"
"license" -> "GPL"
"character_set_client" -> "utf8"
"sql_mode" -> "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
"character_set_results" -> "utf8"
"query_cache_type" -> "OFF"
"init_connect" -> ""
    
    
    com.mysql.jdbc.ConnectionImpl#configureTimezone
    
    //1. configuredTimeZoneOnServer=CST
    
    com.mysql.jdbc.ConnectionPropertiesImpl#getServerTimezone
    // 2. 返回 url 中配置的 serverTimezone： canonicalTimezone = America/New_York
    public String getServerTimezone() {
        return this.serverTimezone.getValueAsString();
    }

	// 3. (getUseTimezone() || !getUseLegacyDatetimeCode()) && configuredTimeZoneOnServer != null)

	// 4. this.serverTimezoneTZ = sun.util.calendar.ZoneInfo[id="America/New_York",offset=-18000000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=America/New_York,offset=-18000000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]


    /**
     * Configures the client's timezone if required.
     * 
     * @throws SQLException
     *             if the timezone the server is configured to use can't be
     *             mapped to a Java timezone.
     */
    private void configureTimezone() throws SQLException {
        String configuredTimeZoneOnServer = this.serverVariables.get("timezone");

        if (configuredTimeZoneOnServer == null) {
            configuredTimeZoneOnServer = this.serverVariables.get("time_zone");

            if ("SYSTEM".equalsIgnoreCase(configuredTimeZoneOnServer)) {
                configuredTimeZoneOnServer = this.serverVariables.get("system_time_zone");
            }
        }

        String canonicalTimezone = getServerTimezone();

        if ((getUseTimezone() || !getUseLegacyDatetimeCode()) && configuredTimeZoneOnServer != null) {
            // user can override this with driver properties, so don't detect if that's the case
            if (canonicalTimezone == null || StringUtils.isEmptyOrWhitespaceOnly(canonicalTimezone)) {
                try {
                    canonicalTimezone = TimeUtil.getCanonicalTimezone(configuredTimeZoneOnServer, getExceptionInterceptor());
                } catch (IllegalArgumentException iae) {
                    throw SQLError.createSQLException(iae.getMessage(), SQLError.SQL_STATE_GENERAL_ERROR, getExceptionInterceptor());
                }
            }
        }

        if (canonicalTimezone != null && canonicalTimezone.length() > 0) {
            this.serverTimezoneTZ = TimeZone.getTimeZone(canonicalTimezone);

            //
            // The Calendar class has the behavior of mapping unknown timezones to 'GMT' instead of throwing an exception, so we must check for this...
            //
            if (!canonicalTimezone.equalsIgnoreCase("GMT") && this.serverTimezoneTZ.getID().equals("GMT")) {
                throw SQLError.createSQLException("No timezone mapping entry for '" + canonicalTimezone + "'", SQLError.SQL_STATE_ILLEGAL_ARGUMENT,
                        getExceptionInterceptor());
            }

            this.isServerTzUTC = !this.serverTimezoneTZ.useDaylightTime() && this.serverTimezoneTZ.getRawOffset() == 0;
        }
    }
1
2
Calendar.DST_OFFSET
Calendar.DST_OFFSET
1
2
3
4
5
6
7
8
9
10
11
12
13
# 相关时区设置和查看命令

SET GLOBAL time_zone = "+5:00";

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
# @@GLOBAL.time_zone	@@SESSION.time_zone
# SYSTEM	SYSTEM


show variables like '%time_zone%';
# Variable_name	Value
# system_time_zone	CST
# time_zone	SYSTEM

CST 是个坑，安装的 Mysql 查看系统时区，显示 CST， 其实是东八区 UTC+8, 可以通过改变会话时区查看 TIMESTAMP 列的值进行验证，但是在 Java 中，CST 指的就是 Central Standard Time， 是 UTC-6, 所以如果客户端，如 Java 程序默认的时区就是 Asia/Shanghai (UTC+8), 不指定任何时区转换设置，操作和显示没有什么问题；Mysql 系统时区变量为 CST, Mysql Server 默认全局时区（System Time Zone - 全局 time_zone 变量） 为 SYSTEM , 继承自系统时区，会话时区默认（Session Time Zone - 局部 time_zone 变量）继承全局时区，最终其实就是 UTC+8。

如果特意在 jdbc url 中指定 serverTimezone=CST:

1
jdbc:mysql://localhost:3306/learning_db?useUnicode=true&characterEncoding=utf8&useLegacyDatetimeCode=true&useTimezone=true&serverTimezone=CST

Mysql 查询 TIMESTAMP 列时，将存储的 UTC+0 时间转换为默认的会话时区时间，默认的会话时区是 UTC+8, 在 Mysql 里显示的是 SYSTEM，继承的 CST(这里虽然是 CST, 其实是 UTC+8, 系统时区默认取机器的时区，通过 date -R, 显示 +8:00), 这时候又指定了 serverTimezone=CST, JDBC 获取的系统时区服务器变量 system_time_zone是 CST, 但不是同一个东西，Java 将这个指定的CST转换为时区对象时，转换的就是 UTC-6, 而在 Mysql 服务器一侧是 UTC+8

1
2
// Java 中，如果是 CST  TimeZone.getTimeZone("CST"), offset=-21600000， 即 'UTC-6'
sun.util.calendar.ZoneInfo[id="CST",offset=-21600000,dstSavings=3600000,useDaylight=true,transitions=235,lastRule=java.util.SimpleTimeZone[id=CST,offset=-21600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=3,startMonth=2,startDay=8,startDayOfWeek=1,startTime=7200000,startTimeMode=0,endMode=3,endMonth=10,endDay=1,endDayOfWeek=1,endTime=7200000,endTimeMode=0]]

这时，反而会有问题，相当于是 UTC-6（指定的Mysql Server会话时区） 转换为 UTC+8 (客户端默认时区)

​ 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# JDBC 查询 TIMESTAMP 列的步骤

1.
默认的会话时区UTC+8
2038-01-19 11:14:07
Mysql服务器  ---------------------------------------------------------------------> JDBC

### JDBC 拿到 TIMESTAMP 列的字节数组转为字符串是 ‘2038-01-19 11:14:07’， 所以第一步应该是这样的；而且改变全局 time_zone 变量，获取的字节数组也发送了变化
## SET GLOBAL time_zone = "+5:00";

2.
serverTimezone=CST	client默认时区=Asia/Shanghai
2038-01-19 11:14:07（CST UTC-6）---->  2038-01-20 13:14:07 (Asia/Shanghai UTC+8)
JDBC------------------------------------------------------------------------------> Java TimeStamp

所以如果真的存在不同客户端时区问题，必要时修改 Mysql 的系统时区为含义明确的时区表示，而不是 CST, 客户端在使用的时候也不用指定 useLegacyDatetimeCode=true&useTimezone=true&serverTimezone=时区， 使用新的 useLegacyDatetimeCode=false 获取时区的方式就可以了，这种会查询 Mysql Server 的时区变量，作为 Server 的时区，因为会话默认也是取的系统时区，所以是一致的，没有什么问题。这时候会将正确的时间转换为客户端时区的时间。

TIMESTAMP 操作真的是很麻烦了，必要时可以采用 廖雪峰-如何正确的处理时间 中策略，存储成 long 型的时间偏移量就好多了，需要比较的话，直接比较的是整数，显示时，再指定特定时区，就可以了。