Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-4629

date/datetime/timestamp with timezone issue

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Resolved
    • Major
    • Resolution: Fixed
    • 4.10.0
    • 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

      Attachments

        1. Phoenix-4629.patch
          2 kB
          Jepson
        2. Phoenix-4629-v2.patch
          10 kB
          Jepson

        Activity

          People

            Unassigned Unassigned
            1028344078@qq.com Jepson
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 96h
                96h
                Remaining:
                Remaining Estimate - 96h
                96h
                Logged:
                Time Spent - Not Specified
                Not Specified