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
