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.
Julian
On Wed, Jul 20, 2016 at 1:41 AM, Yiming Liu <[email protected]> wrote:
> Hi Calcite devs,
>
> I was using Kylin 1.5.2.1 JDBC Driver which is based on Calcite 1.6. I try
> to retrieve a Date column. The origin Date is '2012-01-01', but when I
> called the rs.getString('dateColumn'), I got '2011-12-31'.
>
> I tried to debug this problem. There are some unix timestamp convert, and
> timezone offset shift in Calcite. It's a little complicated there and found
> no test cases related(for DateTimeUtils). The original '2012-01-01' has
> unix timestamp 1325347200000, but from the client side,
> rs.getDate('dateColumn').getTime() returns 1325318400000. The timestamp
> changed. My timezone is GMT+8.
>
> I'm not sure if it is an issue or some configuration I need to set first
> when using Calcite.
>
> --
> With Warm regards
>
> Yiming Liu (刘一鸣)
