Hi Julian, Vladimir,
Israel time zone It is not fixed. Formally, you may find different
abbreviations, like IST and IDT. But in real systems, like Postgres, MS
SQL, or Java, this is a single time zone with DST changes.
The following query in Postgres will return 21:00, 21:00, 22:00 for 24, 25,
and 26 Oct respectively:
select
cast('October 24 00:00:00 2020 Israel' as timestamp with time zone) at
time zone 'UTC',
cast('October 25 00:00:00 2020 Israel' as timestamp with time zone) at
time zone 'UTC',
cast('October 26 00:00:00 2020 Israel' as timestamp with time zone) at
time zone 'UTC'
from t;
Same for SQL Server, even though the zone is named "Israel *Standard* Time"
In Java, the relevant ZoneId is "Israel". And it also returns 21:00, 21:00,
22:00 for these dates:
ZoneId utcZoneId = ZoneId.of("UTC");
ZoneId istZoneId = ZoneId.of("Israel");
LocalDate date24 = LocalDate.of(2020, 10, 24);
LocalDate date25 = LocalDate.of(2020, 10, 25);
LocalDate date26 = LocalDate.of(2020, 10, 26);
System.out.println(ZonedDateTime.ofInstant(date24.atStartOfDay(istZoneId).toInstant(),
utcZoneId));
System.out.println(ZonedDateTime.ofInstant(date25.atStartOfDay(istZoneId).toInstant(),
utcZoneId));
System.out.println(ZonedDateTime.ofInstant(date26.atStartOfDay(istZoneId).toInstant(),
utcZoneId));
I do not have an isolated test for Avatica for now, but I observed the
problem in the real system. The problematic code is in
AbstractCursor.longToDate. The method accepts the absolute GMT time at the
start of the day. E.g., 25-Oct-2020 00:00 GMT. At this time Israel's offset
was +2:00. Then it subtracts that offset, assuming that 25-Oct-2020 00:00
IST == 24-Oct-2020 22:00 GMT. But it is not, because several hours earlier
the offset was +3:00 due to DST. So actually 25-Oct-2020 00:00 IST ==
24-Oct-2020 21:00 GMT. As a result, Avatica will return 22:00 from the
result set, while other databases and Java would return 21:00.
I can write a dedicated test for that, but it would require some time. But
I do not see much value in it during the discussion, because the problem is
relatively clear: the offset at time T2 cannot be used to deduce the offset
at time T1.
This is why other drivers often do some "magic" with Calendar to get the
correct time. Like in PG JDBC [1]. Notice, that for time zones without DST,
they just do some simple math, similarly to Avatica. But for time zones
with DST, they do more complicated calculations to get the correct result.
Regards,
Vladimir.
[1]
https://github.com/pgjdbc/pgjdbc/blob/866c6a9e4cc42d9c279d68b8c756f562eaf0f249/pgjdbc/src/main/java/org/postgresql/jdbc/TimestampUtils.java#L1329
ср, 31 мар. 2021 г. в 23:25, Julian Hyde <[email protected]>:
> Israel Standard Time is fixed at UTC+2. It does not observe daylight
> savings time. So maybe your wall clock should have been in the
> 'Asia/Jerusalem' time zone rather than Israel Standard Time.
>
> On Wed, Mar 31, 2021 at 12:23 PM Vladimir Sitnikov
> <[email protected]> wrote:
> >
> > >Let me provide the exact example
> >
> > Sorry for not being clear.
> > As far as I understand, Avatica means "Java JDBC API" or "something like
> Go
> > API".
> >
> > Could you please provide a test case via Avatica API (e.g. JDBC API)
> along
> > with the actual and expected result?
> > For example, org.apache.calcite.jdbc.CalciteRemoteDriverTest verifies the
> > behavior of Avatica+Calcite
> > integration.
> >
> > Vladimir
>
