[ 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:27 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 these 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)