[
https://issues.apache.org/jira/browse/IGNITE-19371?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-19371:
--------------------------------------
Summary: Sql. Forbid TIME_WITH_LOCAL_TIME_ZONE and
TIMESTAMP_WITH_LOCAL_TIME_ZONE data types. (was: Sql. Deprecate
TIME_WITH_LOCAL_TIME_ZONE and TIMESTAMP_WITH_LOCAL_TIME_ZONE data types.)
> 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
>
> Ignite-3 do not take into account the client's/initiator's timezone in any
> way.
> This will be
>
> 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)
