Let's somehow add this to the FAQ page; I can do it a little bit later together with the 1.5.3 updates.
2016-07-21 8:15 GMT+08:00 ShaoFeng Shi <[email protected]>: > Good sharing, thanks Yiming! > > 2016-07-21 8:01 GMT+08:00 Yiming Liu <[email protected]>: > >> Following is the answer from Julian who is the core Calcite developer: >> >> "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." >> >> 2016-07-20 17:43 GMT+08:00 Yiming Liu <[email protected]>: >> >> > it's not a Kylin bug, but from Calcite date processing. Has sent help >> > request to Calcite community. Will update when get answer. >> > >> > 2016-07-20 15:14 GMT+08:00 Yiming Liu <[email protected]>: >> > >> >> Hi Kylin devs, >> >> >> >> Running against the sample learn_kylin project on the latest master >> >> branch: select part_dt, sum(price) as total_selled, count(distinct >> >> seller_id) as sellers from kylin_sales where part_dt = DATE'2012-01-01' >> >> group by part_dt order by part_dt; >> >> >> >> I got the result from Web GUI: >> >> 2012-01-01 >> >> 466.9037 >> >> 12 >> >> >> >> >> >> >> >> But if I run the same query through JDBC Driver, >> >> >> >> try { >> >> Driver driver = (Driver) >> Class.forName("org.apache.kylin.jdbc.Driver").newInstance(); >> >> Properties info = new Properties(); >> >> info.put("user", "ADMIN"); >> >> info.put("password", "KYLIN"); >> >> Connection conn = driver.connect("jdbc:kylin:// >> 192.168.1.108:7070/learn_kylin", info); >> >> final String sql = "select part_dt, sum(price) as total_selled, >> count(distinct seller_id) as sellers from kylin_sales where part_dt = >> DATE'2012-01-01' group by part_dt order by part_dt"; >> >> >> >> PreparedStatement stmt = conn.prepareStatement(sql); >> >> try { >> >> try (ResultSet rs = stmt.executeQuery()) { >> >> while (rs.next()) { >> >> >> >> System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)); >> >> } >> >> } >> >> } finally { >> >> stmt.close(); >> >> } >> >> >> >> I got the result: >> >> 2011-12-31 466.9037 12 >> >> >> >> It seems something wrong when JDBC parse the result. >> >> >> >> -- >> >> With Warm regards >> >> >> >> Yiming Liu (刘一鸣) >> >> >> > >> > >> > >> > -- >> > With Warm regards >> > >> > Yiming Liu (刘一鸣) >> > >> >> >> >> -- >> With Warm regards >> >> Yiming Liu (刘一鸣) >> > > > > -- > Best regards, > > Shaofeng Shi > > -- Best regards, Shaofeng Shi
