[PHOENIX-4629] date/datetime/timestamp with timezone issue

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.10.0
• Fix Version/s: 4.10.0
• Labels: patch

Environment

phoenix4.10-hbase1.2

• Flags: Patch
• External issue ID: PHOENIX-3221 PHOENIX-997 PHOENIX-1485
• Assignee: Unassigned
• Reporter: Jepson
• Created: 27/Feb/18 03:11
• Updated: 12/Mar/18 11:26
• Resolved: 12/Mar/18 10:37

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