It works very well. Thanks Julian. 2016-07-22 5:02 GMT+08:00 Julian Hyde <[email protected]>:
> 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 (刘一鸣) > > -- With Warm regards Yiming Liu (刘一鸣)
