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.
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. 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}
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 }
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.
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.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
