Re: How to set timezone in JDBC parameter?

[Li Yang <li...@apache.org>]

This is a common problem when converting Java dates to/from SQL dates. It
can be resolved by correctly setting the time zone at JDBC API calls.

Following is the answer from Julian from the Apache Calcite community:

"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."

Read more from the original post
<http://apache-kylin.74782.x6.nabble.com/JDBC-query-result-Date-column-get-wrong-value-td5370.html>
.

On Tue, Aug 29, 2017 at 10:26 PM, apache_dev@163.com <ap...@163.com>
wrote:

> Hi,
>   when i query data by kylin  insight(dimension table join   column  in
> hive  is date type,data is 20170731),query result is rigth(have setting
> :kylin.web.timezone=GMT+8)
> but query by jdbc the result data is 20170330.
>
>  After setting in jdbc connect .
>
>  Properties info = new Properties();
>
> info.put("timeZone", "GMT +08");
>
>
> rs.getDate(part_dt) ,result is 20170731
>
> rs.getString(part_dt),result is 20170330.
>
> rs is java resultset
>
> What's the reason?
>
> How can I set the result right(getString(part_dt) is 20170331)?
>
>
>
>
>
>
>
>
>
> apache_dev@163.com
>
> From: apache_dev@163.com
> Date: 2017-08-29 15:50
> To: dev
> Subject: Re: How to set timezone in JDBC parameter?
> Hi,
>
>   This problem have now been sorted, Thinks
>
>   Properties info = new Properties();
>
> info.put("timeZone", "GMT +08");
>
>
>
> apache_dev@163.com
> From: apache_dev@163.com
> Date: 2017-08-29 11:57
> To: dev
> Subject: How to set timezone in JDBC parameter?
> Hi,
>   when i query data by kylin  insight(dimension table join   column  in
> hive  is date type,data is 20170731),query result is rigth(have setting
> :kylin.web.timezone=GMT+8)
> but query by jdbc the result data is 20170329.
>   How to set time zone in JDBC parameter?
> Thinks.
>
> apache_dev@163.com
>