You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by Yiming Liu <li...@gmail.com> on 2016/07/20 07:14:59 UTC
JDBC query result Date column get wrong value
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 (刘一鸣)
Re: JDBC query result Date column get wrong value
Posted by ShaoFeng Shi <sh...@apache.org>.
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 <sh...@apache.org>:
> Good sharing, thanks Yiming!
>
> 2016-07-21 8:01 GMT+08:00 Yiming Liu <li...@gmail.com>:
>
>> 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 <li...@gmail.com>:
>>
>> > 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 <li...@gmail.com>:
>> >
>> >> 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
Re: JDBC query result Date column get wrong value
Posted by ShaoFeng Shi <sh...@apache.org>.
Good sharing, thanks Yiming!
2016-07-21 8:01 GMT+08:00 Yiming Liu <li...@gmail.com>:
> 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 <li...@gmail.com>:
>
> > 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 <li...@gmail.com>:
> >
> >> 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
Re: JDBC query result Date column get wrong value
Posted by Yiming Liu <li...@gmail.com>.
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 <li...@gmail.com>:
> 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 <li...@gmail.com>:
>
>> 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 (刘一鸣)
Re: JDBC query result Date column get wrong value
Posted by Yiming Liu <li...@gmail.com>.
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 <li...@gmail.com>:
> 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 (刘一鸣)