Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
Tony (& others), On 2020-03-09 19:48, Tony Whyman wrote: The question then arises as to why this is not the normal way of working? Using the client's local ICU introduces a maintenance headache. This argument I personally hardly accept. May be for old windows versions - yes, some problems. All the others upgrade ICU as a part of regular OS upgrade. I.e. if one already has local ICU extended format is definitely useless overhead. On the other hand I know that some people treat installing ICU even single time as a huge problem - for example a number of users from sql.ru. Taking into an account that both POVs do exist we have an ability to change modes. And as long as something should be chosen as default - shorter format is default. I hope we can finish with this discussion at least... If it is out-of-step with the server (or other clients) then inconsistent results may occur when computing daylight savings time offsets. So why shouldn't I just always call "SET BIND..." as soon as a database connection is opened, support only the EXTENDED TIME/TIMESTAMP WITH TIME ZONE, and avoid the risk of ICU's getting out of step? Certainly if you prefer this way to go you can choose it. Be sure - nobody is going to remove extended formats as long as they are in use. BTW, you can add that kind of tuning to DPB with one additional bonus - bindings from DPB become default for a session, i.e. they are restored after session reset. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
10.03.2020 09:48, Alex Peshkoff via Firebird-devel wrote: May be for old windows versions - yes, some problems. All the others upgrade ICU as a part of regular OS upgrade. I.e. if one already has local ICU extended format is definitely useless overhead. ICU appeared only in recent builds of Windows 10. Windows 7-8 and servers don't have it. Some Linux distros package ICU in a very strange way with names that Firebird don't know how to use. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
On 2020-03-09 16:04, Adriano dos Santos Fernandes wrote: On 08/03/2020 06:24, Mark Rotteveel wrote: On 05-03-2020 17:14, Adriano dos Santos Fernandes wrote: The JDBC specification doesn't support named time zones, only offset time zones (OffsetDateTime/OffsetTime), Please point me to that text. JDBC 4.2 specification, section 3.1: """ * Additional Mappings to Table B-4, Mapping from Java Object to JDBC Types [..] Added support to map java.time.LocalOffsetTime to JDBC TIME_WITH_TIMEZONE. Added support to map java.time.LocalOffsetDateTime to JDBC TIMESTAMP_WITH_TIMEZONE. * Additional Mappings to Table B-5, Performed by setObject and setNull between Java Object Types and Target JDBC Types [..] Allow conversion of java.time.OffsetTime to CHAR, VARCHAR, LONGVARCHAR, and TIME_WITH_TIMESTAMP. Allow conversion of java.time.OffsetDateTime to CHAR, VARCHAR, LONGVARCHAR, TIME_WITH_TIMESTAMP and TIMESTAMP_WITH_TIMESTAMP. """ So, also tables B.4, B.5, B.6 in JDBC 4.2 (and 4.3). so I must convert to an offset-based data type. AFAIK it talks about defaults but support custom mappings. I'm keeping the option open to support ZonedDateTime in the future, but as that is not specified by the JDBC specification, those would be a non-standard extension. And non-standard extensions are just not a good idea when as a minor JDBC driver, you need to work with tools expecting standard behaviour. Also, I'm talking about getDate, for example. JDBC does not specify a mapping for getDate, getTime, and getTimestamp for WITH TIME ZONE types (see table B.6). Jaybird does implement it as a compatibility measure, but I recommend not using it (see also below). Using the new java.time types is the new standard since Java 8, so usage of getDate (and getTime and getTimestamp) is discouraged since JDBC 4.2 (in favour of using java.time.LocalDate, java.time.LocalTime and java.time.LocalDateTime (which are all without time zone information). The types java.sql.Time, java.sql.Timestamp and java.sql.Date are badly defined and are historically troublesome, because they are technically a thin wrapper around epoch milliseconds, while they are specified as the WITHOUT TIME ZONE value in the JVM default zone (which wreaks all kinds of havoc with applications in different zones). Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
On 2020-03-09 17:48, Tony Whyman wrote: My reading is that when the server's ICU is used, TIMESTAMP/TIME with TIME ZONE values are returned as UTC with both the time zone id and the time zone offset as computed by the server's ICU taking into account any daylight savings time variations. I presume that in extended mode, when a TIMESTAMP/TIME with TIME ZONE is used as a statement parameter, the value is passed to the server as a local time plus the time zone id and the server's ICU is used to convert this to UTC before evaluating the query. To be clear, WITH TIME ZONE values are passed as UTC values both in the normal and 'extended' mode. The only difference is that the normal format has 2 bytes (4 bytes over the wire) with the offset in minutes **or** the zone id, and the 'extended' format has an additional 2 bytes (4 bytes over the wire) that always contain the offset in minutes (so for offset based values, extended will contain the offset twice, and a named zone based value will contain a zoneid and the offset. If you want them as local time values, you must bind to 'LEGACY' (the WITHOUT TIME ZONE types). Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
On 2020-03-09 16:12, Adriano dos Santos Fernandes wrote: On 08/03/2020 06:24, Mark Rotteveel wrote: Please enlighten me in what way will I have "misunderstood the thing completely", and will "Jaybird users [..] have a very wrong behavior forever"? That I talked in almost every message here in this list about the topic. If you (driver and applications developers) want to support only half thinked scenarios like happens every time in Brazil, where cellphone operators for example incorrectly changes mobile phones times more than once every year, making people lost or go to their commitments one hour earlier, then you can implement wrong handling. This of course does not happens only due to bad implementations, but also to not updated time zones databases, but for sure simple implementations also make these bugs. If you as a good developer thinks that the above things are bugs made by bad developers and that the time zone system exists in a way that such things should never occur, then you implement and support named zones correctly. This vague anecdote doesn't really answer my question. Mark Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Introduction of EXTENDED TIME/TIMESTAMP WITH TIME ZONE?
On Tue, 10 Mar 2020 11:34:40 +0100 Dimitry Sibiryakov wrote: > 10.03.2020 09:48, Alex Peshkoff via Firebird-devel wrote: > > May be for old windows versions - yes, some problems. All the others > > upgrade ICU as a part > of regular OS upgrade. I.e. if one already has > > local ICU extended format is definitely > useless overhead. > >ICU appeared only in recent builds of Windows 10. Windows 7-8 and > servers don't have it. Some Linux distros package ICU in a very strange way > with names that Firebird don't know how to use. > > One of the perennial problems with building firebird on opensuse is the non-standard version numbering that opensuse use. Of course, the icu version required by firebird 3 is different to firebird 4 and they are both different from the icu deployed with the released version of opensuse. I've tried hacking the firebird code without much success and I've tried applying patches that have worked in the past, again without much success. Soemtimes they work. Sometimes not. I usually end up just digging around for community repos to find a version of icu that will build with firebird. But that doesn't solve the underlying problem. Another aspect, which is more personal, is that like most Anglophones I don't really need the character support provided by icu. And living in a country with a single time zone I don't need the time zone support either. For me icu spells Aaargh!!! :-) Paul -- Paul Reeves http://www.ibphoenix.com Supporting users of Firebird Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
