JDBC query result Date column get wrong value

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

JDBC query result Date column get wrong value

Billy(Yiming) Liu
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 (刘一鸣)
Reply | Threaded
Open this post in threaded view
|

Re: JDBC query result Date column get wrong value

Billy(Yiming) Liu
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 <[hidden email]>:

> 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 (刘一鸣)
Reply | Threaded
Open this post in threaded view
|

Re: JDBC query result Date column get wrong value

Billy(Yiming) Liu
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 <[hidden email]>:

> 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 <[hidden email]>:
>
>> 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 (刘一鸣)
Reply | Threaded
Open this post in threaded view
|

Re: JDBC query result Date column get wrong value

shaofengshi
Good sharing, thanks Yiming!

2016-07-21 8:01 GMT+08:00 Yiming Liu <[hidden email]>:

> 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 <[hidden email]>:
>
> > 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 <[hidden email]>:
> >
> >> 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
Reply | Threaded
Open this post in threaded view
|

Re: JDBC query result Date column get wrong value

shaofengshi
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 <[hidden email]>:

> Good sharing, thanks Yiming!
>
> 2016-07-21 8:01 GMT+08:00 Yiming Liu <[hidden email]>:
>
>> 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 <[hidden email]>:
>>
>> > 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 <[hidden email]>:
>> >
>> >> 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