30.08.2019 20:41, Mark Rotteveel wrote:
Hope Alex will answer this, below is my own understanding of propositions.
Could you explicitly describe the problem you think this solves, and how your
proposal solves it?
Hmm... the first paragraph at original message explains it
BTW: This subject says TIME WITH TZ, but I assume it also applies to TIMESTAMP
(with time zone)
Sure
On 2019-08-30 13:48, Alex Peshkoff via Firebird-devel wrote:
Hi all!
From discussion in a thread "Could not find acceptable ICUlibrary"
it's getting clear that we can not satisfy all requirements to time
with timezone fields (when exchanging client/server messages) in
single format because they (requirements) are too contradictory.
Suggested solution is use of flexible format letting each client to
receive data from the server in a best fit way. To tune that format
three parameters will be used - together they make it possible to have
6 different formats - 4 variants of native time with timezone, legacy
format and character string.
Term 'regional time' will later be used to reference time in a
timezone initially used to enter that time. I.e. when one enters
06:15:30 in GMT+3 timezone this is regional time which corresponds to
UTC 03:15:30. For client in GMT-3 timezone corresponding local time
will be 00:15:30. We have special field format for that local time
therefore receiving local time for time with timezone is not a
problem. All that was said here to emphasize difference between local
and regional time to avoid misunderstanding.
It is unclear to me exactly what format is going to be used. When using 'regional time', will the offset or zone information still
be included?
Yes
1. Let's use SQL subtype in order to represent in the message UTC or
regional time, i.e. for time with time zone 2 subtypes will make sense
- UTC format or regional format. When user provides message format
information in execute/prepare this unambiguously defines format of
time with timezone.
I assume these subtypes will only be used for communication between server and client, and that storage will always apply UTC
storage (which I assume will be subtype 0), right?
Yes
To be honest, clients overriding this in the message format sounds like a
complex feature that in practice will not be used.
Pay attention - when time is returned as UTC
initial regional code or offset is anyway present in timezone. That
makes time information as flexible as possible. Appropriate session
setting is required to make server know what format should be used by
default:
ALTER SESSION SET TIME WITH TIME ZONE TO {UTC | REGIONAL}
This should also be available as a DPB item, and the value of the DPB is the
value used after ALTER SESSION RESET.
Agree
I also assume that when this setting is used, that bind information returned by the server supplies the appropriate subtype (as
defined above).
Sure
2. Server may be forced to always use offset instead time zone code
when sending data to the client. Returning to our first example with
offset == +3 - if time was originally entered as 06:15:30 MSK (I do
not remember zone code, therefore it's name is used) it will be
returned as 03:15:30 (time part in UTC) or 06:15:30 (time part in
regional MSK) depending upon previous setting and offset part will
contain +3:00 (offset corrsponding to MSK region). Suggested syntax:
ALTER SESSION FORCE TIME ZONE OFFSET { ON | OFF }
Sounds like a good thing. This should also be available as a DPB item, and the value of the DPB is the value used after ALTER
SESSION RESET.
Agree
Notice - as long as we do not support seconds precison in offsets an
attempt to convert time with such timezone code to time with offset
will raise error. Luckily that timezones are historical and not
supposed to be used too often. If one really needs to use such
timezone let him work with timezone code using regional time or using
plane UTC.
This part isn't entirely clear, do you mean only for time zone with a
sub-minute precision?
AFAIU, yes
3. This solution is for really old clients which anyway can not work
with new time formats - use of alternate binding to character string
(like with decimal float values). Syntax is more or less same:
SET TIME ZONE BIND {NATIVE | LEGACY | CHAR}
This makes it possible to view time on the client as it was originally
entered in human-readable way. And as long as the client anyway does
not understand new format of time that's probably the best we can
suggest and enough.
This already exists (except option CHAR).
Sure, the proposition is to add new option the the existing statement.
Regards,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
