[jira] [Comment Edited] (IGNITE-19274) Sql. Jdbc client. Support TIMESTAMP WITH LOCAL TIME ZONE type

["Pavel Pereslegin (Jira)" <ji...@apache.org>]

    [ 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/26/24 12:14 PM:
---------------------------------------------------------------------

outdated comment (see update section in issue)


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 (check other DB)) 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.


> Sql. Jdbc client.  Support TIMESTAMP WITH LOCAL TIME ZONE type
> --------------------------------------------------------------
>
>                 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
>            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.
> h5. Update from 26.02.2024:
> Definition of done for this task:
> * Client time zone is passed to server (check other database implementations to decide how and when to pass it).
> * Data of type "TIMESTAMP With LOCAL TIME ZONE" can be written/read correctly using the dynamic parameter.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)