[
https://issues.apache.org/jira/browse/IGNITE-19274?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17716595#comment-17716595
]
Pavel Pereslegin edited comment on IGNITE-19274 at 2/6/24 11:26 AM:
--------------------------------------------------------------------
The issue described in the task is one of the problems in terms of supporting
work with the client's timezone.
*The problem - Ignite-3 do not take into account the client's/initiator's
timezone in any way.*
We have the following "data types mapping":
||Calcite type||JDBC Type||Current Ignite type||
|TIME|java.sql.Time|java.time.LocalTime|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|
*_WITH_LOCAL_TIME_ZONE types must take into account the client's local time
zone. Those. if the client is in the "GMT+3" time zone and is passing the value
"14:00", the value must be converted from GMT+3 to UTC and the value "11:00"
must be stored in the database.
It seems that *from KV side* there is no problem with
{{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}, because client must set {{Instant}} value
for the field (and {{instant}} value is always in UTC)
*From sql side* client must pass somehow (per-connection or per-query?)
client's timezone, that must be taken into account by sql-engine on server side.
{{TIME_WITH_LOCAL_TIME_ZONE}} must be mapped to another type (not the same as
{{TIME}})).
We also need to design how default values for this types will be materialized.
was (Author: xtern):
The issue described in the task is one of the problems in terms of supporting
work with the client's timezone.
*The problem - Ignite-3 do not take into account the client's/initiator's
timezone in any way.*
We have the following "data types mapping":
||Calcite type||JDBC Type||Current Ignite type||
|TIME|java.sql.Time|java.time.LocalTime|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|
*_WITH_LOCAL_TIME_ZONE types must take into account the client's local time
zone. Those. if the client is in the "GMT+3" time zone and is passing the value
"14:00", the value must be converted from GMT+3 to UTC and the value "11:00"
must be stored in the database.
It seems that *from KV side* there is no problem with
{{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}, because client must set {{Instant}} value
for the field (and instant value is always in UTC)
*From sql side* client must pass somehow (per-connection or per-query?)
client's timezone, that must be taken into account by sql-engine on server side.
{{TIME_WITH_LOCAL_TIME_ZONE}} must be mapped to another type (not the same as
{{TIME}})).
We also need to design how default values for this types will be materialized.
> Sql. Jdbc side working with TIMESTAMP WITH LOCAL TIME ZONE did not take into
> account current tz while storing data.
> -------------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-19274
> URL: https://issues.apache.org/jira/browse/IGNITE-19274
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Affects Versions: 3.0.0-beta1
> Reporter: Evgeny Stanilovsky
> Assignee: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> The {{TIMESTAMP WITH LOCAL TIME ZONE}} data type is a variant of
> {{TIMESTAMP}} that includes a time zone offset in its value. Data stored in
> the database is normalized to the database time zone (UTC) and time zone
> offset is not stored as part of the column data. When the data is retrieved,
> it to be returned in the user's local session time zone.
> i.e:
> {noformat}
> CREATE TABLE timestamp(ts TIMESTAMP, t_tz TIMESTAMP WITH LOCAL TIME ZONE);
> SET TIME ZONE 'tz1';
> INSERT INTO timestamp VALUES ('2011-01-01 01:01:01', TIMESTAMP WITH LOCAL
> TIME ZONE '2011-01-01 01:01:01');
> SET TIME ZONE 'tz2';
> INSERT INTO timestamp VALUES ('2011-01-01 01:01:01', TIMESTAMP WITH LOCAL
> TIME ZONE '2011-01-01 01:01:01');
> ...
> select * from timestamp;{noformat}
> returned rows need to be different in case of different tz1 and tz2 offsets
> but they are equals for now. Also returned representation need to be present
> in user session time zone.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
