[
https://issues.apache.org/jira/browse/PHOENIX-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17691729#comment-17691729
]
Istvan Toth edited comment on PHOENIX-5066 at 2/21/23 6:11 PM:
---------------------------------------------------------------
My initial patch looks promising.
The next problem - apart from fixing a gazillion tests - is backwards
compatibility with the current broken implementation and with
DATE_FORMAT_TIMEZONE.
We still want the behaviour to be switchable per client connection.
Thankfully the above changes are strictly client-side, so we can implement them.
The current connection parameters:
DATE_FORMAT_TIMEZONE: The old workaround attempt
DATE/TIME/TIMESTAMP_FORMAT: the parse/format string
Each of the above can be set in hbase-site.xml, or as a connection parameter.
Each of the above is works by setting parameters in the PhoenixConnection
object (and has convenience acccessors in StatmentContext)
_DATE_FORMAT_TIMEZONE_ is used directly as a default value forthe TO_DATE,
TO_TIME, TO_TIMESTAMP functions when the TZ is omitted. The substitution
happens during query compilation.
_DATE/TIME/TIMESTAMP_FORMAT_ is used directly by the TO_DATE, TO_NUMBER,
TO_DATE, TO_TIME? TO_NUMBER functions as a default value during query
compilation.
{_}date/time/timestamp formatter{_}s are intermediate object generated and
stored in PhoenixConnection from _DATE_FORMAT_TIMEZONE_ and
_DATE/TIME/TIMESTAMP_FORMAT._ These are used in ResultSet.getString(), and as
default values when compiling TO_CHAR and TO_NUMBER expressions. (and for
EXPLAIN generation)
The timeZone parameters to be added:
COMPLIANT_TIMEZONE_HANDLING : by default false for backwards compatibility. If
set to true, enables the new code.
_COMPLIANT_TIMEZONE_HANDLING_ is the the switch enabling the new
implementation. I am undecided wheter enabling it should disable
DATE_FORMAT_TIMEZONE or not. Currently I think that we can leave that decision
with the user.
_COMPLIANT_TIMEZONE_HANDLING_ __ is enabled, then we are correcting the
epoch-like temporal types when written or read in
PreparedStatement.setParameter() or rs.getDate().. and getObject().
I also considered letting the user override the client timezone from a
connection parameter, but decided against it. The whole point of the timezone
correction is to revert the effective local->UTC conversion performed by
java.util.Date class, so using a different timezone than the one used by
java.util.Date is unlikely generate any coherent data.
was (Author: stoty):
My initial patch looks promising.
The next problem - apart from fixing a gazillion tests - is backwards
compatibility with the current broken implementation and with
DATE_FORMAT_TIMEZONE.
We still want the behaviour to be switchable per client connection.
Thankfully the above changes are strictly client-side, so we can implement them.
The current connection parameters:
DATE_FORMAT_TIMEZONE: The old workaround attempt
DATE/TIME/TIMESTAMP_FORMAT: the parse/format string
Each of the above can be set in hbase-site.xml, or as a connection parameter.
Each of the above is works by setting parameters in the PhoenixConnection
object (and has convenience acccessors in StatmentContext)
_DATE_FORMAT_TIMEZONE_ is used directly as a default value forthe TO_DATE,
TO_TIME, TO_TIMESTAMP functions when the TZ is omitted. The substitution
happens during query compilation.
_DATE/TIME/TIMESTAMP_FORMAT_ is used directly by the TO_DATE, TO_NUMBER,
TO_DATE, TO_TIME? TO_NUMBER functions as a default value during query
compilation.
{_}date/time/timestamp formatter{_}s are intermediate object generated and
stored in PhoenixConnection from _DATE_FORMAT_TIMEZONE_ and
_DATE/TIME/TIMESTAMP_FORMAT._ These are used in ResultSet.getString(), and as
default values when compiling TO_CHAR and TO_NUMBER expressions. (and for
EXPLAIN generation)
The timeZone parameters to be added:
> 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: Istvan Toth
> Priority: Critical
> Fix For: 5.3.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, PHOENIX-5066.master.v6.patch
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> 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.20.10#820010)
