[ https://issues.apache.org/jira/browse/PHOENIX-4629?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jepson updated PHOENIX-4629: ---------------------------- Attachment: Phoenix-4629-v2.patch > timestamp with timezone issue > ----------------------------- > > Key: PHOENIX-4629 > URL: https://issues.apache.org/jira/browse/PHOENIX-4629 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.10.0 > Environment: phoenix4.10-hbase1.2 > Reporter: Jepson > Priority: Major > Attachments: Phoenix-4629-v2.patch, Phoenix-4629.patch > > Original Estimate: 96h > Remaining Estimate: 96h > > *1.Create timezonetest table:* > {code:java} > 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';{code} > *2.Create TimestampTest.java* > {code: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(); > } > } > {code} > *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 > *{color:#ff0000}minus 8 hours, is also wrong.{color}* > *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 > {code:java} > public static final String DEFAULT_TIME_ZONE_ID = "GMT"; > public static final String LOCAL_TIME_ZONE_ID = "LOCAL";{code} > *Changed:* > {code:java} > public static final String DEFAULT_TIME_ZONE_ID = "Asia/Shanghai"; > public static final String LOCAL_TIME_ZONE_ID = "Asia/Shanghai"; > {code} > --------------------------------------------------------------------------------- > {code:java} > private final DateTimeFormatter formatter = > ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("UTC"));{code} > *Changed:* > {code:java} > private final DateTimeFormatter formatter = > ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("Asia/Shanghai")); > {code} > > 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 -- This message was sent by Atlassian JIRA (v7.6.3#76005)