Phoenix关于时区的处理方式说明

一、社区版Phoenix时间相关类型介绍

时间数据处理是数据开发者经常遇到的问题，众所周知时间都是跟时区相关的，如果对于时区处理不当，会造成时间数据错误，进而引入一系列棘手的问题。Phoenix中跟时间相关的类型有TIMESTAMP，DATE和TIME，这些类型对于时区的处理逻辑是相同的，后面笔者就以TIMESTAMP类型为例来说明Phoenix关于时区的处理方式。首先，我们先来看下Phoenix文档中对于TIMESTAMP类型的描述：

The timestamp data type. The format is yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]. Mapped to java.sql.Timestamp with an internal representation of the number of nanos from the epoch. The binary representation is 12 bytes: an 8 byte long for the epoch time plus a 4 byte integer for the nanos. Note that the internal representation is based on a number of milliseconds since the epoch (which is based on a time in GMT), while java.sql.Timestamp will format timestamps based on the client's local time zone.

这段描述中明确指出TIMESTAMP类型在处理时是基于GMT时区的毫秒值（默认的基准都是"1970-01-01 00:00:00.000"），而java.sql.Timestamp使用的是客户端的本地时区。下面我们通过一个例子来说明这个设定在实际使用中，容易遇到的问题。

Statement stmt = con.createStatement();
stmt.execute("drop table test");
stmt.execute("create table test(mykey integer primary key, mytime timestamp)");
stmt.execute("upsert into test values(1, '2018-11-11 10:00:00.000')");
PreparedStatement pstmt = con.prepareStatement("upsert into test values(?, ?)");
pstmt.setInt(1, 2);
pstmt.setTimestamp(2, Timestamp.valueOf("2018-11-11 10:00:00.000"));
pstmt.executeUpdate();
con.commit();
stmt.execute("select * from test");
ResultSet rs = stmt.getResultSet();
System.out.println("select without filter results:");
while (rs.next()) {
    System.out.println(rs.getInt(1) + " : " + rs.getString(2) + " : " + rs.getTimestamp(2));
}
stmt.execute("select * from test where mytime = timestamp'2018-11-11 10:00:00.000'");
rs = stmt.getResultSet();
System.out.println("select with statement:");
while (rs.next()) {
    System.out.println(rs.getInt(1) + " : " + rs.getString(2) + " : " + rs.getTimestamp(2));
}
pstmt = con.prepareStatement("select * from test where mytime = ?");
pstmt.setTimestamp(1, Timestamp.valueOf("2018-11-11 10:00:00.000"));
pstmt.execute();
rs = pstmt.getResultSet();
System.out.println("select with preparedStatement:");
while (rs.next()) {
    System.out.println(rs.getInt(1) + " : " + rs.getString(2) + " : " + rs.getTimestamp(2));
}

结果输出如下：

select without filter results:
1 : 2018-11-11 10:00:00.000 : 2018-11-11 18:00:00.0
2 : 2018-11-11 02:00:00.000 : 2018-11-11 10:00:00.0
select with statement:
1 : 2018-11-11 10:00:00.000 : 2018-11-11 18:00:00.0
select with preparedStatement:
2 : 2018-11-11 02:00:00.000 : 2018-11-11 10:00:00.0

我们可以发现以下规律:

1. 用string写入用getTimestamp读取时时间戳多了8个小时；而用setTimestamp写入，用getString读出时间戳则少了8个小时。
2. 当查询时，按照字符串的方式拼where条件只能匹配到使用string写入的数据，而用setTimestamp设置where条件中的字段只能匹配到用setTimestamp方式写入的时间戳。

需要指出的是，当我们使用客户端也就是sqlline.py时，只能是用字符串写入，然后字符串读出。用户经常遇到的使用场景是，在线系统用 setTimestamp写入，然后会用sqlline.py做查询，或者用getString在页面展示，这个时候就会出现多8个小时的情况；而做条件过滤时，用户一定要注意使用方式，否则会出现匹配不到的情况，而当使用sqlline查询时，必须使用convert_tz方法做时区转换才能得到正确结果。

回过头来，我们再来看开源Phoenix内部关于时区的实现逻辑，进一步理解文档中关于时区的表述。java.sql.Timestamp类型是带时区的，默认是本地时区，且不能通过函数参数设置。Phoenix在做String和Timestamp转换时使用的是GMT时区，也可以认为不带时区。比如对于"1970-01-01 08:00:00.000"，Phoenix存储的数值是28800000，而Timestamp.valueOf("1970-01-01 08:00:00.000").getTime()得到的数值则是0，两者混用就会出现偏差。这个逻辑也是造成程序测试结果的根本原因。

此外，上面提到的是Phoenix重客户端的逻辑，而Phoenix轻客户端对于时区的处理跟Phoenix重客户端也有不一样的地方。我们使用前面完全相同的逻辑，在实现中把jdbc url串换成轻客户端的格式，打印结果如下：

select without filter results:
1 : 2018-11-11 10:00:00 : 2018-11-11 10:00:00.0
2 : 2018-11-11 02:00:00 : 2018-11-11 02:00:00.0
select with statement:
1 : 2018-11-11 10:00:00 : 2018-11-11 10:00:00.0
select with preparedStatement:
2 : 2018-11-11 02:00:00 : 2018-11-11 02:00:00.0

我们可以发现以下规律：

1. 打印的时候轻客户端的getString和getTimestamp的结果是一样的，且和重客户端的getString保持一致。
2. 写入和查询的时候轻客户端和重客户端逻辑一样。

这是由于社区版轻客户端在实现getTimestamp的时候，在构造Timestamp对象之前先把得到的毫秒数值减去了时区，而其他操作都是直接透传给重客户端实现的。

通过以上描述，我们可以发现Phoenix对于时区的处理非常复杂，稍不留意就会出错。更严重的，如果用户在写入的时候混用了拼SQL语句和setTimestamp的方式，会导致脏数据，并且是没有办法区分的。

不要混用两种方式！字符串拼SQL和对象设置PreparedStatement，只选一种，不管是读还是写。

二、阿里云Phoenix对时区问题的解决

首先，我们先看下传统开源数据库中对于时区问题处理方法。

在ANSI SQL标准中，TIMESTAMP类型分两种，分别是TIMESTAMP WITH TIMEZONE和TIMESTAMP，前一种是考虑时区的，后一种是不考虑时区的。在MYSQL中TIMESTAMP类型是默认带时区的，用户输入的如果不指定时区，默认是本地时区，在实际存储时会转变为GMT时区，当用户读取时再转化为本地时区；而不带时区的类型在MYSQL中是DATETIME类型，用户在调用getTimestamp接口时，会根据DATETIME的年月日时分秒构造出来Timestamp对象，这样用户通过getString和getTimestamp拿到的时间始终是一致的。

PostgresSQL对于时区的处理跟MYSQL不同，PG的TIMESTAMP类型是不带时区的，而TIMESTAMPTZ是带时区的。处理的逻辑同MYSQL类似，只是内部存储和实现上会有不同，这里不再赘述。文末附有MYSQL和PG对于时区的参考文档，感兴趣的读者可以进一步研究。有一点相同的是，不管MYSQL和PG怎么实现和表述，在用户使用的过程中都不会像开源Phoenix那么让人困惑。

阿里云团队在Phoenix 5.x版本中对时区问题进行了统一解决，不管用户使用轻客户端和重客户端，都不会再像以前那么费解。实现逻辑跟MYSQL类似，也就是，TIMESTAMP类型在实际存储时都是使用GMT时区，用户使用客户端读写时，会根据本地时区进行转化。不管用户使用轻客户端还是重客户端，在写入时使用statement还是PreparedStatement，在读取时使用getString还是getTimestamp，在查询时使用拼字符串还是setTimestamp等，拿到的结果都是一致，容易理解且符合预期的。

我们同样使用前文提到的测试程序，把Phoenix版本改成阿里云版本的Phoenix 5.x，得到的结果如下：

select without filter results:
1 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0
2 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0
select with statement:
1 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0
2 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0
select with preparedStatement:
1 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0
2 : 2018-11-11 10:00:00.000 : 2018-11-11 10:00:00.0

三、参考文献

http://phoenix.apache.org/language/datatypes.html#timestamp_type

https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

https://www.postgresql.org/docs/current/datatype-datetime.html