You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Josh Elser (Jira)" <ji...@apache.org> on 2020/10/19 17:44:00 UTC
[jira] [Commented] (PHOENIX-5066) The TimeZone is incorrectly used
during writing or reading data
[ https://issues.apache.org/jira/browse/PHOENIX-5066?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17216965#comment-17216965 ]
Josh Elser commented on PHOENIX-5066:
-------------------------------------
{quote}Let's say I want to write 2020-05-12 12:26:26 (and I am in GMT+2 timezone) the server has it's phoenix.query.dateFormatTimeZone property on the default GMT.
So what I would like to see is that the object that we store in HBase should be the byte[] equivalent of 2020-05-12 10:26:26 GMT.
But when we read this data again it should be represented in our local timezone and get 2020-05-12 12:26:26 GMT+2 back even if we use the getObject or getString methods.
{quote}
IMO this is the correct solution to the problem that actually scales out to different groups using a database. I worry that Janaai's suggestion won't work when you have multiple teams/applications trying to read the same table. If everyone can agree "date/time is converted to GMT through Phoenix", then each team can make sure that their application's timezone is set appropriately and they should be able to get "stable" results from Phoenix.
Let me take another look at your PR :)
> 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: 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, 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.3.4#803005)