[ https://issues.apache.org/jira/browse/PHOENIX-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Jaanai updated PHOENIX-5066: ---------------------------- Description: We have two methods to write data when uses JDBC API. #1. Uses _the exceuteUpdate_ method to execute a string that is an upsert SQL. #2. Uses the _prepareStatement_ method to set some object and execute. The _string_ data needs to convert to a new object by the schema information of tables. we'll use some date formatters to convert string data to object for Date/Time/Timestamp types when writes data and the formatters are used when reads data as well. *Uses default timezone test* Writing 3 records by the different ways. {code:java} UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 15:40:47','2018-12-10 15:40:47') UPSERT INTO date_test VALUES (2,to_date('2018-12-10 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, time);stmt.setTimestamp(4, ts); {code} Reading the table by the getObject(getDate/getTime/getTimestamp) methods. {code:java} 1 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 2 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 3 | 2018-12-10 | 15:45:07 | 2018-12-10 15:45:07.66 {code} Reading the table by the getString methods {code:java} 1 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 2 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 3 | 2018-12-10 07:45:07.660 | 2018-12-10 07:45:07.660 | 2018-12-10 07:45:07.660 {code} *Uses GMT+8 test* Writing 3 records by the different ways. {code:java} UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 15:40:47','2018-12-10 15:40:47') UPSERT INTO date_test VALUES (2,to_date('2018-12-10 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, time);stmt.setTimestamp(4, ts); {code} Reading the table by the getObject(getDate/getTime/getTimestamp) methods. {code:java} 1 | 2018-12-10 | 23:40:47 | 2018-12-10 23:40:47.0 2 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.0 3 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.106 {code} Reading the table by the getString methods {code:java} 1 | 2018-12-10 23:40:47.000 | 2018-12-10 23:40:47.000 | 2018-12-10 23:40:47.000 2 | 2018-12-10 15:40:47.000 | 2018-12-10 15:40:47.000 | 2018-12-10 15:40:47.000 3 | 2018-12-10 15:40:47.106 | 2018-12-10 15:40:47.106 | 2018-12-10 15:40:47.106 {code} _We_ have a historical problem, we'll parse the string to Date/Time/Timestamp objects with timezone in #1, which means the actual data is going to be changed when stored in HBase table。 was: We have two methods to write data when uses JDBC API. #1. Uses _the exceuteUpdate_ method to execute a string that is an upsert SQL. #2. Uses the _prepareStatement_ method to set some object and execute. The _string_ data needs to convert to a new object by the schema information of tables. we'll use some date formatters to convert string data to object for Date/Time/Timestamp types when writes data and the formatters are used when reads data as well. ## Uses default timezone test Writing 3 records by the different ways. {code:java} UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 15:40:47','2018-12-10 15:40:47') UPSERT INTO date_test VALUES (2,to_date('2018-12-10 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, time);stmt.setTimestamp(4, ts); {code} Reading the table by the getObject(getDate/getTime/getTimestamp) methods. {code:java} 1 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 2 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 3 | 2018-12-10 | 15:45:07 | 2018-12-10 15:45:07.66 {code} Reading the table by the getString methods {code:java} 1 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 2 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 3 | 2018-12-10 07:45:07.660 | 2018-12-10 07:45:07.660 | 2018-12-10 07:45:07.660 {code} ## Uses GMT+8 test Writing 3 records by the different ways. {code:java} UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 15:40:47','2018-12-10 15:40:47') UPSERT INTO date_test VALUES (2,to_date('2018-12-10 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, time);stmt.setTimestamp(4, ts); {code} Reading the table by the getObject(getDate/getTime/getTimestamp) methods. {code:java} 1 | 2018-12-10 | 23:40:47 | 2018-12-10 23:40:47.0 2 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.0 3 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.106 {code} Reading the table by the getString methods {code:java} 1 | 2018-12-10 23:40:47.000 | 2018-12-10 23:40:47.000 | 2018-12-10 23:40:47.000 2 | 2018-12-10 15:40:47.000 | 2018-12-10 15:40:47.000 | 2018-12-10 15:40:47.000 3 | 2018-12-10 15:40:47.106 | 2018-12-10 15:40:47.106 | 2018-12-10 15:40:47.106 {code} _We_ have a historical problem, we'll parse the string to Date/Time/Timestamp objects with timezone in #1, which means the actual data is going to be changed when stored in HBase table。 > The TimeZone is incorrectly used during writing or reading data > --------------------------------------------------------------- > > Key: PHOENIX-5066 > URL: https://issues.apache.org/jira/browse/PHOENIX-5066 > Project: Phoenix > Issue Type: Bug > Affects Versions: 5.0.0, 4.14.1 > Reporter: Jaanai > Assignee: Jaanai > Priority: Critical > Fix For: 4.15.0, 5.1 > > > We have two methods to write data when uses JDBC API. > #1. Uses _the exceuteUpdate_ method to execute a string that is an upsert SQL. > #2. Uses the _prepareStatement_ method to set some object and execute. > The _string_ data needs to convert to a new object by the schema information > of tables. we'll use some date formatters to convert string data to object > for Date/Time/Timestamp types when writes data and the formatters are used > when reads data as well. > > *Uses default timezone test* > Writing 3 records by the different ways. > {code:java} > UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 > 15:40:47','2018-12-10 15:40:47') > UPSERT INTO date_test VALUES (2,to_date('2018-12-10 > 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) > stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, > time);stmt.setTimestamp(4, ts); > {code} > Reading the table by the getObject(getDate/getTime/getTimestamp) methods. > {code:java} > 1 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 > 2 | 2018-12-10 | 23:45:07 | 2018-12-10 23:45:07.0 > 3 | 2018-12-10 | 15:45:07 | 2018-12-10 15:45:07.66 > {code} > Reading the table by the getString methods > {code:java} > 1 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 > 15:45:07.000 > 2 | 2018-12-10 15:45:07.000 | 2018-12-10 15:45:07.000 | 2018-12-10 > 15:45:07.000 > 3 | 2018-12-10 07:45:07.660 | 2018-12-10 07:45:07.660 | 2018-12-10 > 07:45:07.660 > {code} > > *Uses GMT+8 test* > Writing 3 records by the different ways. > {code:java} > UPSERT INTO date_test VALUES (1,'2018-12-10 15:40:47','2018-12-10 > 15:40:47','2018-12-10 15:40:47') > UPSERT INTO date_test VALUES (2,to_date('2018-12-10 > 15:40:47'),to_time('2018-12-10 15:40:47'),to_timestamp('2018-12-10 15:40:47')) > stmt.setInt(1, 3);stmt.setDate(2, date);stmt.setTime(3, > time);stmt.setTimestamp(4, ts); > {code} > Reading the table by the getObject(getDate/getTime/getTimestamp) methods. > {code:java} > 1 | 2018-12-10 | 23:40:47 | 2018-12-10 23:40:47.0 > 2 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.0 > 3 | 2018-12-10 | 15:40:47 | 2018-12-10 15:40:47.106 {code} > Reading the table by the getString methods > {code:java} > 1 | 2018-12-10 23:40:47.000 | 2018-12-10 23:40:47.000 | 2018-12-10 > 23:40:47.000 > 2 | 2018-12-10 15:40:47.000 | 2018-12-10 15:40:47.000 | 2018-12-10 > 15:40:47.000 > 3 | 2018-12-10 15:40:47.106 | 2018-12-10 15:40:47.106 | 2018-12-10 > 15:40:47.106 > {code} > > _We_ have a historical problem, we'll parse the string to > Date/Time/Timestamp objects with timezone in #1, which means the actual data > is going to be changed when stored in HBase table。 -- This message was sent by Atlassian JIRA (v7.6.3#76005)