phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jaanai (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (PHOENIX-5066) The TimeZone is incorrectly used during writing or reading data
Date Mon, 10 Dec 2018 13:43:00 GMT

     [ 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 objects 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
>
>         Attachments: DateTest.java
>
>
> 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 objects 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)

Mime
View raw message