[PHOENIX-5066] The TimeZone is incorrectly used during writing or reading data

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 5.0.0, 4.14.1
• Fix Version/s: 5.2.0, 5.1.4

Release Note

Adds the phoenix.query.applyTimeZoneDisplacement attribute, which enables applying timezone displacement when setting or retriteving java.sql. time types via PreparedSatetement and ResultSet objects.

Setting the attribute will result in interpreting the epoch-based java.sql time types in the local timezone.

This attribute is off by default for backwards compatibility reasons, and needs to be enabled explicitly. This is a client side attribute, and can be set on a per connection basis.

• Assignee: Istvan Toth
• Reporter: Jaanai Zhang
• Created: 10/Dec/18 10:19
• Updated: 05/May/23 06:44
• Resolved: 08/Mar/23 04:24

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.

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);

Reading the table by the getObject(getDate/getTime/getTimestamp) methods.

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 

Reading the table by the getString methods 

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

 Uses GMT+8 test

 Writing 3 records by the different ways.

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);

Reading the table by the getObject(getDate/getTime/getTimestamp) methods.

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 

Reading the table by the getString methods

 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

 

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。

Issue Links

• causes: PHOENIX-6949 Use j.u.Date.getTimeOffset() for displacement calculation
• causes: PHOENIX-4664 Time Python driver tests failing
• is caused by: PHOENIX-1485 Date columns should be displayed without timezone offset
• is depended upon by: PHOENIX-6882 Umbrella Ticket for date/time handling issues
• is duplicated by: PHOENIX-6623 Phoenix Spark reading DATE datatype value less than one day from phoenix table
• relates to: PHOENIX-6792 FastDateFormat cannot parse greater than milisecond precision.
• relates to: PHOENIX-6822 Multiplier for Temporal round/floor/ceiling functions is not implemented and/or poorly defined
• relates to: PHOENIX-4664 Time Python driver tests failing
• relates to: PHOENIX-6823 calling Joda-based round() function on temporal PK field causes division by zero error
• relates to: PHOENIX-4822 The configuration "phoenix.query.dateFormatTimeZone" does't work on the client
• split to: PHOENIX-6895 Consider implementing WITH TIMEZONE temporal types
• links to: GitHub Pull Request #796
• links to: GitHub Pull Request #1504
• links to: GitHub Pull Request #1567
• links to: GitHub Pull Request #1577
• links to: PR with WITHOUT TIMEZONE semantics