[
https://issues.apache.org/jira/browse/CALCITE-2743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16725527#comment-16725527
]
Marc Prud'hommeaux commented on CALCITE-2743:
---------------------------------------------
We are seeing this problem as well. I believe it is due to a workaround for
MySQL Timestamp issues that is being applied throughout Calcite irrespective of
the data source, such that the offset to the local time to UTC is always being
applied to the timestamp:
[https://github.com/apache/calcite/blob/3fa29455664bec0056c436491b369e0cd72242ea/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcUtils.java#L159]
The following test case demonstrates the issue just using a ReflectiveSchema:
{code:java}
class TestTimestampOffsets extends TestCase {
public static Timestamp defaultTimestamp = Timestamp.valueOf("2018-12-25
12:00:00");
// Passes
public void testTimestampUTC() throws SQLException {
queryTimestamp(TimeZone.getTimeZone("UTC"));
}
// Fails: Expected: 2018-12-25 11:00:00.0 Actual: 2018-12-25 17:00:00.0
public void testTimestampChicago() throws SQLException {
queryTimestamp(TimeZone.getTimeZone("America/Chicago"));
}
public void queryTimestamp(TimeZone zone) throws SQLException {
var info = new Properties();
var conn = DriverManager.getConnection("jdbc:calcite:", info);
conn.unwrap(CalciteConnection.class).getRootSchema().add("TSCHEMA", new
ReflectiveSchema(new TSCHEMA()));
var deftz = TimeZone.getDefault();
TimeZone.setDefault(zone);
try {
try (var rs = conn.createStatement().executeQuery("SELECT TS FROM
TSCHEMA.TTABLE")) {
assertTrue(rs.next());
assertEquals(defaultTimestamp, rs.getObject(1));
}
} finally {
TimeZone.setDefault(deftz); // restore default TZ
}
}
public static class TSCHEMA {
public TemporalTable[] TTABLE = new TemporalTable[] { new
TemporalTable() };
public static class TemporalTable {
public Timestamp TS = defaultTimestamp;
}
}
}
{code}
> TimeStamp confused in avatica jdbc
> ----------------------------------
>
> Key: CALCITE-2743
> URL: https://issues.apache.org/jira/browse/CALCITE-2743
> Project: Calcite
> Issue Type: Bug
> Components: avatica
> Affects Versions: 1.10.0
> Reporter: shining
> Priority: Major
>
> I use Phoenix Query Server through avatica, Operation is as follows：
> 1. create table with sqlline-thin
>
> CREATE TABLE test_timezone(log_time TIMESTAMP NOT NULL PRIMARY KEY, id
> VARCHAR(40));
> 2. Upset data
>
> upsert into test_timezone values('2018-11-27 11:01:59.000','1’);
> 3. Query
> 0: jdbc:phoenix:thin:url=http://localhost:876> select * from
> test_timezone;
> +------------------------------+-----+
> | LOG_TIME | ID |
> +-----------------------------+------+
> | 2018-11-27 03:01:59 | 1 |
> +------------------------------+——+
> My local timeZone is GMT+8, and configured Phoenix
> “phoenix.query.dateFormatTimeZone=GMT+8”
> I also view code of avatica， when the timezone is GMT+8， getTimeStamp method
> will lose 8 hours：
> public Timestamp getTimestamp(Calendar calendar) throws SQLException {
> java.util.Date date = (java.util.Date) getObject();
> if (date == null) {
> return null;
> }
> long v = date.getTime();
> if (calendar != null) {
> v -= calendar.getTimeZone().getOffset(v);
> }
> return new Timestamp(v);
> }
> sqlline-thin use getString() method get the timestamp，it pass a null timezone
> to timestampAsString()
> So I have two doubtful places here：
> 1）I get correct time from phoenixResultSet， why reduce 8 hours ？
> 2）Can getString method be returned by getTimeStamp().toString():
> public String getString() throws SQLException {
> final long v = getLong();
> if (v == 0 && wasNull()) {
> return null;
> }
> return getTimeStamp(v, null).toString();
> }
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
