[
https://issues.apache.org/jira/browse/IGNITE-19371?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-19371:
--------------------------------------
Description:
Currently Ignite-3 do not take into account the client's/initiator's timezone
in any way.
This will be resolved in IGNITE-19274.
We need to temporarily disable the use of these types:
TIME_WITH_LOCAL_TIME_ZONE
Apache Calcite supports the following "timezone aware" datatypes.
* TIMESTAMP WITH LOCAL TIME ZONE
* TIME WITH LOCAL TIME ZONE
The implementation is similar to Oracle "TIMESTAMP WITH LOCAL TIME ZONE" and
PostgreSQL "TIMESTAMP WITH TIME ZONE".
>From Oracle documentation:
"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. "
Usage example:
{noformat}
Client 1 (GMT+1)
insert time '11:00' -> stores '10:00' (in DB)
select time -> returns '11:00'
Client 2 (GMT+2)
select time -> returns '12:00'
{noformat}
*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||Type contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
*_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.
h2. Proposal.
There are two implementation options, it all depends on when we will transfer
the client's time zone.
h2. 1. Transfer TimeZone from client per connection.
In this case the timezone from client will be stored per connection.
{code:java}
connect()
// save client's time zone to context/session
insert (time with local time zone '14:00')
// store: value - timezone_offset = '11:00' (UTC)
select time with local time zone
// read: value from DB '11:00' UTC
// transfer: value + timezone_offset
// returns: LocalTime
getTime()
// return value{code}
In this case all offsets will be applied on the server side, those
timestamp_with_time_zone we'll need to be LocalDateTime.
Since the timezone will only be set for each connection. Changing the client's
time zone during connection will have no effect.
E,g,
{code:java}
set timeZone='GMT+1'
connect()
insert (time with local time zone '14:00') // db stores '13:00'
select time -> returns '14:00'
set timeZone='GMT+2'
select time -> returns '14:00'
reconnect()
select time -> returns '15:00'{code}
As a result we'll have the following data types.
||Calcite type||JDBC Type||Current Ignite type||contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.LocalDateTime|no|
h2. 2. Transfer TimeZone from client per query.
In this case, we will need to add some special type instead of LocalTime
because it does not store timezone information.
Since we will have time zone information on the client, we will simply
translate the value to the local ghb time zone when it is displayed.
Those. the scheme of work is approximately the following.
{code:java}
connect()
insert (time with local time zone '14:00')
// put client's timezone from the request to execution context.
// store: value - timezone_offset = '11:00' (UTC)
select time with local time zone
// read: value from DB '11:00' UTC
// returns: LocalTimeTz
getTime()
// if (value instanceof LocalTimeTz)
// read UTC '11:00' + local_timezone_offset{code}
As a result we'll have the following types:
||Calcite type||JDBC Type||Current Ignite type||Type contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTimeTz|yes|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
But usually no one does this, the time zone is transmitted when connected to
the server.
And we need to separately discover how this approach will work with a Ignite
thin client.
was:
Currently Ignite-3 do not take into account the client's/initiator's timezone
in any way.
This will be resolved in IGNITE-19274.
We need to temporarily disable the use of these types:
Apache Calcite supports the following "timezone aware" datatypes.
* TIMESTAMP WITH LOCAL TIME ZONE
* TIME WITH LOCAL TIME ZONE
The implementation is similar to Oracle "TIMESTAMP WITH LOCAL TIME ZONE" and
PostgreSQL "TIMESTAMP WITH TIME ZONE".
>From Oracle documentation:
"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. "
Usage example:
{noformat}
Client 1 (GMT+1)
insert time '11:00' -> stores '10:00' (in DB)
select time -> returns '11:00'
Client 2 (GMT+2)
select time -> returns '12:00'
{noformat}
*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||Type contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
*_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.
h2. Proposal.
There are two implementation options, it all depends on when we will transfer
the client's time zone.
h2. 1. Transfer TimeZone from client per connection.
In this case the timezone from client will be stored per connection.
{code:java}
connect()
// save client's time zone to context/session
insert (time with local time zone '14:00')
// store: value - timezone_offset = '11:00' (UTC)
select time with local time zone
// read: value from DB '11:00' UTC
// transfer: value + timezone_offset
// returns: LocalTime
getTime()
// return value{code}
In this case all offsets will be applied on the server side, those
timestamp_with_time_zone we'll need to be LocalDateTime.
Since the timezone will only be set for each connection. Changing the client's
time zone during connection will have no effect.
E,g,
{code:java}
set timeZone='GMT+1'
connect()
insert (time with local time zone '14:00') // db stores '13:00'
select time -> returns '14:00'
set timeZone='GMT+2'
select time -> returns '14:00'
reconnect()
select time -> returns '15:00'{code}
As a result we'll have the following data types.
||Calcite type||JDBC Type||Current Ignite type||contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.LocalDateTime|no|
h2. 2. Transfer TimeZone from client per query.
In this case, we will need to add some special type instead of LocalTime
because it does not store timezone information.
Since we will have time zone information on the client, we will simply
translate the value to the local ghb time zone when it is displayed.
Those. the scheme of work is approximately the following.
{code:java}
connect()
insert (time with local time zone '14:00')
// put client's timezone from the request to execution context.
// store: value - timezone_offset = '11:00' (UTC)
select time with local time zone
// read: value from DB '11:00' UTC
// returns: LocalTimeTz
getTime()
// if (value instanceof LocalTimeTz)
// read UTC '11:00' + local_timezone_offset{code}
As a result we'll have the following types:
||Calcite type||JDBC Type||Current Ignite type||Type contains timezone info||
|TIME|java.sql.Time|java.time.LocalTime|no|
|TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTimeTz|yes|
|TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
|TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
But usually no one does this, the time zone is transmitted when connected to
the server.
And we need to separately discover how this approach will work with a Ignite
thin client.
> Sql. Forbid TIME_WITH_LOCAL_TIME_ZONE and TIMESTAMP_WITH_LOCAL_TIME_ZONE data
> types.
> ------------------------------------------------------------------------------------
>
> Key: IGNITE-19371
> URL: https://issues.apache.org/jira/browse/IGNITE-19371
> Project: Ignite
> Issue Type: Improvement
> Reporter: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> Currently Ignite-3 do not take into account the client's/initiator's timezone
> in any way.
> This will be resolved in IGNITE-19274.
> We need to temporarily disable the use of these types:
> TIME_WITH_LOCAL_TIME_ZONE
>
> Apache Calcite supports the following "timezone aware" datatypes.
> * TIMESTAMP WITH LOCAL TIME ZONE
> * TIME WITH LOCAL TIME ZONE
> The implementation is similar to Oracle "TIMESTAMP WITH LOCAL TIME ZONE" and
> PostgreSQL "TIMESTAMP WITH TIME ZONE".
> From Oracle documentation:
> "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. "
> Usage example:
> {noformat}
> Client 1 (GMT+1)
> insert time '11:00' -> stores '10:00' (in DB)
> select time -> returns '11:00'
> Client 2 (GMT+2)
> select time -> returns '12:00'
> {noformat}
>
> *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||Type contains timezone info||
> |TIME|java.sql.Time|java.time.LocalTime|no|
> |TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
> |TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
> |TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
> *_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.
> h2. Proposal.
> There are two implementation options, it all depends on when we will transfer
> the client's time zone.
> h2. 1. Transfer TimeZone from client per connection.
> In this case the timezone from client will be stored per connection.
> {code:java}
> connect()
> // save client's time zone to context/session
>
> insert (time with local time zone '14:00')
> // store: value - timezone_offset = '11:00' (UTC)
>
> select time with local time zone
> // read: value from DB '11:00' UTC
> // transfer: value + timezone_offset
> // returns: LocalTime
>
> getTime()
> // return value{code}
> In this case all offsets will be applied on the server side, those
> timestamp_with_time_zone we'll need to be LocalDateTime.
> Since the timezone will only be set for each connection. Changing the
> client's time zone during connection will have no effect.
> E,g,
> {code:java}
> set timeZone='GMT+1'
> connect()
> insert (time with local time zone '14:00') // db stores '13:00'
> select time -> returns '14:00'
> set timeZone='GMT+2'
> select time -> returns '14:00'
> reconnect()
> select time -> returns '15:00'{code}
> As a result we'll have the following data types.
> ||Calcite type||JDBC Type||Current Ignite type||contains timezone info||
> |TIME|java.sql.Time|java.time.LocalTime|no|
> |TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTime|no|
> |TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
> |TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.LocalDateTime|no|
> h2. 2. Transfer TimeZone from client per query.
> In this case, we will need to add some special type instead of LocalTime
> because it does not store timezone information.
> Since we will have time zone information on the client, we will simply
> translate the value to the local ghb time zone when it is displayed.
>
> Those. the scheme of work is approximately the following.
> {code:java}
> connect()
>
> insert (time with local time zone '14:00')
> // put client's timezone from the request to execution context.
> // store: value - timezone_offset = '11:00' (UTC)
>
> select time with local time zone
> // read: value from DB '11:00' UTC
> // returns: LocalTimeTz
>
> getTime()
> // if (value instanceof LocalTimeTz)
> // read UTC '11:00' + local_timezone_offset{code}
> As a result we'll have the following types:
> ||Calcite type||JDBC Type||Current Ignite type||Type contains timezone info||
> |TIME|java.sql.Time|java.time.LocalTime|no|
> |TIME_WITH_LOCAL_TIME_ZONE|java.sql.Time|java.time.LocalTimeTz|yes|
> |TIMESTAMP|java.sql.Timestamp|java.time.LocalDateTime|no|
> |TIMESTAMP_WITH_LOCAL_TIME_ZONE|java.sql.Timestamp|java.time.Instant|yea|
> But usually no one does this, the time zone is transmitted when connected to
> the server.
> And we need to separately discover how this approach will work with a Ignite
> thin client.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
