Jepson created PHOENIX-4629:
-------------------------------
Summary: timestamp with timezone issue
Key: PHOENIX-4629
URL: https://issues.apache.org/jira/browse/PHOENIX-4629
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.10.0
Environment: phoenix4.10-hbase1.2
Reporter: Jepson
*1.Create timezonetest table:*
{code:java}
CREATE TABLE JYDW.timezonetest (
id bigint(11) not null primary key,
date_c date ,
datetime_c timestamp ,
timestamp_c timestamp
)SALT_BUCKETS = 12, COMPRESSION='SNAPPY';{code}
*2.Create TimestampTest.java*
{code:java}
package org.apache.phoenix.jdbc;
import org.apache.phoenix.query.BaseConnectionlessQueryTest;
import org.apache.phoenix.query.QueryServices;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Properties;
/**
* Created by Jepson on 2017/11/2.
*
CREATE TABLE JYDW.timezonetest (
id bigint(11) not null primary key,
date_c date ,
datetime_c timestamp ,
timestamp_c timestamp
)SALT_BUCKETS = 12, COMPRESSION='SNAPPY';
*/
public class TimestampTest extends BaseConnectionlessQueryTest {
public static void main(String[] args) throws Exception {
Properties props = new Properties();
// props.setProperty(QueryServices.DATE_FORMAT_TIMEZONE_ATTRIB,
"Asia/Shanghai");
String url =
"jdbc:phoenix:192.168.117.137,192.168.117.138,192.168.117.140,192.168.117.141,192.168.117.142:2181:/hbase";
//Connection conn = DriverManager.getConnection(url,props);
Connection conn = DriverManager.getConnection(url);
conn.createStatement().execute("UPSERT INTO
jydw.TIMEZONETEST(id,date_c,datetime_c,timestamp_c) \n" +
"values(101,'2018-02-25','2018-02-25 00:00:00','2018-02-25
10:00:00')");
conn.commit();
ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM
TIMEZONETEST");
while(rs.next()) {
System.out.println(rs.getString("id")+" : " +
rs.getString("date_c")+" : " + rs.getString("datetime_c")+" : " +
rs.getString("timestamp_c"));
}
rs.close();
conn.close();
}
}
{code}
*3.Run the TimestampTest.java,the console print message:*
*id : date_c : datetime_c : timestamp_c*
101 : 2018-02-24 16:00:00.000 : 2018-02-24 16:00:00.000 : 2018-02-25
02:00:00.000
100 : 2018-02-24 16:00:00.000 : 2018-02-24 16:00:00.000 : 2018-02-25
02:00:00.000
*{color:#ff0000}minus 8 hours, is also wrong.{color}*
*4.Reference these, not work*
https://issues.apache.org/jira/browse/PHOENIX-997
https://issues.apache.org/jira/browse/PHOENIX-1485
5.Modify DateUtil.java
{code:java}
public static final String DEFAULT_TIME_ZONE_ID = "GMT";
public static final String LOCAL_TIME_ZONE_ID = "LOCAL";{code}
*Changed:*
{code:java}
public static final String DEFAULT_TIME_ZONE_ID = "Asia/Shanghai";
public static final String LOCAL_TIME_ZONE_ID = "Asia/Shanghai";
{code}
---------------------------------------------------------------------------------
{code:java}
private final DateTimeFormatter formatter =
ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("UTC"));{code}
*Changed:*
{code:java}
private final DateTimeFormatter formatter =
ISO_DATE_TIME_FORMATTER.withZone(DateTimeZone.forID("Asia/Shanghai"));
{code}
6.Again run *TimestampTest.java, the result is ok.*
*id : date_c : datetime_c : timestamp_c*
101 : 2018-02-25 00:00:00.000 : 2018-02-25 00:00:00.000 : 2018-02-25
10:00:00.000
100 : 2018-02-25 00:00:00.000 : 2018-02-25 00:00:00.000 : 2018-02-25
10:00:00.000
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
