Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
4.10.0
-
None
-
phoenix4.10-hbase1.2
Description
1.Create timezonetest table:
CREATE TABLE JYDW.timezonetest ( id bigint(11) not null primary key, date_c date , datetime_c timestamp , timestamp_c timestamp )SALT_BUCKETS = 12, COMPRESSION='SNAPPY';
2.Create TimestampTest.java
package org.apache.phoenix.jdbc; import org.apache.phoenix.query.BaseConnectionlessQueryTest; import org.apache.phoenix.query.QueryServices; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.util.Properties; /** * Created by Jepson on 2017/11/2. * CREATE TABLE JYDW.timezonetest ( id bigint(11) not null primary key, date_c date , datetime_c timestamp , timestamp_c timestamp )SALT_BUCKETS = 12, COMPRESSION='SNAPPY'; */ public class TimestampTest extends BaseConnectionlessQueryTest { public static void main(String[] args) throws Exception { Properties props = new Properties(); // props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB, "Asia/Shanghai"); String url = "jdbc:phoenix:192.168.117.137,192.168.117.138,192.168.117.140,192.168.117.141,192.168.117.142:2181:/hbase"; //Connection conn = DriverManager.getConnection(url,props); Connection conn = DriverManager.getConnection(url); conn.createStatement().execute("UPSERT INTO jydw.TIMEZONETEST(id,date_c,datetime_c,timestamp_c) \n" + "values(101,'2018-02-25','2018-02-25 00:00:00','2018-02-25 10:00:00')"); conn.commit(); ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM TIMEZONETEST"); while(rs.next()) { System.out.println(rs.getString("id")+" : " + rs.getString("date_c")+" : " + rs.getString("datetime_c")+" : " + rs.getString("timestamp_c")); } rs.close(); conn.close(); } }
3.Run the TimestampTest.java,the console print message:
id : date_c : datetime_c : timestamp_c
101 : 2018-02-24 16:00:00.000 : 2018-02-24 16:00:00.000 : 2018-02-25 02:00:00.000
100 : 2018-02-24 16:00:00.000 : 2018-02-24 16:00:00.000 : 2018-02-25 02:00:00.000
minus 8 hours, is also wrong.
4.Reference these, not work
https://issues.apache.org/jira/browse/PHOENIX-997
https://issues.apache.org/jira/browse/PHOENIX-1485
5.Modify DateUtil.java
public static final String DEFAULT_TIME_ZONE_ID = "GMT"; public static final String LOCAL_TIME_ZONE_ID = "LOCAL";
Changed:
public static final String DEFAULT_TIME_ZONE_ID = "Asia/Shanghai"; public static final String LOCAL_TIME_ZONE_ID = "Asia/Shanghai";
---------------------------------------------------------------------------------
private final DateTimeFormatter formatter = ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("UTC"));
Changed:
private final DateTimeFormatter formatter = ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("Asia/Shanghai"));
6.Again run TimestampTest.java, the result is ok.
id : date_c : datetime_c : timestamp_c
101 : 2018-02-25 00:00:00.000 : 2018-02-25 00:00:00.000 : 2018-02-25 10:00:00.000
100 : 2018-02-25 00:00:00.000 : 2018-02-25 00:00:00.000 : 2018-02-25 10:00:00.000