You’re welcome. Please let me know whether it solves your problem. If not, please log a JIRA case and we can track it down.
> On Jul 20, 2016, at 4:59 PM, Yiming Liu <[email protected]> wrote: > > Thanks Julian. The explain is very clear. > > > 2016-07-21 1:37 GMT+08:00 Julian Hyde <[email protected]>: > >> Calcite is implementing the SQL standard, which says that date-time >> values have no time zone, and JDBC, which converts zoneless date-time >> values into the local timezone when you call a method such as >> getDate(String). >> >> Consider the timestamp literal TIMESTAMP '1970-01-01 00:00:00'. In the >> database that has the value 0. But does it represent the epoch >> (1970-01-01 00:00:00 UTC)? No. There is no time zone. >> >> Unlike SQL date-time values, Java date-time values (java.util.Date, >> java.sql.Timestamp etc.) represent a moment in time, and their >> timezone is always UTC. So, converting from a SQL date-time to a JDBC >> date-time (and vice versa) requires a time zone. >> >> For example, when you read that value using "Timestamp >> ResultSet.getTimestamp(String)" you are implicitly saying "assume that >> the value is in my JVM's local time zone". So, we're looking at the >> value "1970-01-01 00:00:00 GMT+8" and converting it to a UTC value, >> which gives -28,800,000. (When it was midnight on 1970-01-01 in China, >> it was 4pm on 1969-12-31 in Greenwich.) >> >> If you've stored my date-time values in UTC, you should specify a >> time-zone when retrieving, by using a Calendar object. Then >> Calcite/Avatica will not apply a timezone shift the value when it >> reads it: >> >> ResultSet rs; >> TimeZone tzUtc = TimeZone.getTimeZone("UTC"); >> Calendar cUtc = Calendar.getInstance(tzUtc); >> Timestamp ts = rs.getTimestamp("dateColumn", cUtc); >> System.out.println(ts.getTime()); // prints 0 >> >> The same timezone-shifting problem can also occur on the way in. Make >> sure the value in the database really is 0. If it isn't, use >> PreparedStatement.setTimestamp(0, cUtc) to prevent the shift. >> >> Julian >> >> >> On Wed, Jul 20, 2016 at 1:41 AM, Yiming Liu <[email protected]> >> wrote: >>> Hi Calcite devs, >>> >>> I was using Kylin 1.5.2.1 JDBC Driver which is based on Calcite 1.6. I >> try >>> to retrieve a Date column. The origin Date is '2012-01-01', but when I >>> called the rs.getString('dateColumn'), I got '2011-12-31'. >>> >>> I tried to debug this problem. There are some unix timestamp convert, and >>> timezone offset shift in Calcite. It's a little complicated there and >> found >>> no test cases related(for DateTimeUtils). The original '2012-01-01' has >>> unix timestamp 1325347200000, but from the client side, >>> rs.getDate('dateColumn').getTime() returns 1325318400000. The timestamp >>> changed. My timezone is GMT+8. >>> >>> I'm not sure if it is an issue or some configuration I need to set first >>> when using Calcite. >>> >>> -- >>> With Warm regards >>> >>> Yiming Liu (刘一鸣) >> > > > > -- > With Warm regards > > Yiming Liu (刘一鸣)
