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 (刘一鸣)
