[
https://issues.apache.org/jira/browse/PHOENIX-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17106412#comment-17106412
]
Istvan Toth commented on PHOENIX-5066:
--------------------------------------
Some thoughts about this:
Goals, in order of decreasing importance:
* Return the same value that was inserted to the DB
* The data should be the same, regardless the Input/Output formatting
* Do not surprise the users
* Follow SQL semantics as much as possible
* Make it possible to use local timezones
* Backwards compatibility
The current behaviour is so broken, that it is not trivial to discern the
original intent
of the Phoenix implementation, but here is my take on it:
* Be compatible with HBase, which generally uses unix timestamps in UTC
* Interpret and store all times as GMT
* With an option to specify the timezone that is used when setting/reading the
time.
* All temporal types are handled as absolute time (i.e. java Instant), as
opposed to calendar types (i.e. Java LocalDateTime), unlike most databases,
where both are used depending on the specific SQL type.
Taking the above int account, I suggest the following behaviour:
* Store all temporal data as GMT/UTC
* Use/Abuse the unfortunately named *DATE_FORMAT_TIMEZONE_ATTRIB* as the
timezone for all temporal types represented via the JDBC interface (including
java.sql.Time, String formats, literal parsing, literal printing, date function
semantics (i.e. floor(), ceil(), etc) )
* By default DATE_FORMAT_TIMEZONE_ATTRIB is GMT, which will break strict JDBC
compatibility, that states that Instant types should be interpreted/returned in
the JDK timezone.
* DATE_FORMAT_TIMEZONE_ATTRIB has a special value LOCAL, which mostly mimics
the JDBC standard behaviour for Instant types.
> 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 Zhang
> Assignee: Richard Antal
> Priority: Critical
> Fix For: 4.15.1, 5.1.1, 4.16.0
>
> Attachments: DateTest.java, PHOENIX-5066.4x.v1.patch,
> PHOENIX-5066.4x.v2.patch, PHOENIX-5066.4x.v3.patch,
> PHOENIX-5066.master.v1.patch, PHOENIX-5066.master.v2.patch,
> PHOENIX-5066.master.v3.patch, PHOENIX-5066.master.v4.patch,
> PHOENIX-5066.master.v5.patch
>
>
> 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
(v8.3.4#803005)
