30.08.2019 19:15, Adriano dos Santos Fernandes wrote:
On 30/08/2019 08:48, Alex Peshkoff via Firebird-devel wrote: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}Maybe you didn't understood how the things work. Time is *already* returned in UTC with a additional region code. If a client cannot or do not want to work with the region, it should just use the time value. Why do you want to introduce a setting that adds no value?
If client can't work with region code, it can't display time in *original* (regional) format. UTC time is non-sence for end-user who expect to see regional time. In this case client could ask server to convert time portion of TSTZ value into region time (instead of UTC). Then client just display time and region name (easily obtained from region code without usage of external facilities, such as ICU). For end user it is transparent operation. Yes, client could SET TIME BIND LEGACY, but in this case it will not be able to display region name and it have to change internal bindings from TSTZ to TS.
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 }Server should not be forced to send *incorrect* data. Offset is different from offset extracted from region.
It is exaggeration in most cases. If such offect is correct or not correct or good enough correct - let the user decide by himself.
Anyone living in a country with unstable time zones (defined by politics) known that and for example you cannot schedule things on a calendar for even in the next year correctly using offsets.
It is not related with our subject. We can't fix all the things out of our control. But we can add some flexibility. BTW, i think that TIME WITH TIMEZONE should not be allowed to use region code at all. Because it is impossible to convert region code to offset without date, in general.
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.NATIVE and LEGACY is already present. CHAR is probably a good thing too, of course, always returning with the region when it was used.
Sure Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
