> Avatica assumes that the underlying engine operates on relative time > without time zone as opposed to global time (UTC).
This is correct. This is standard behavior for SQL's TIMESTAMP data type. (Hopefully Avatica's behavior for TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME is different.) > D2 00:00 GMT+2 -> D1 22:00 GMT > D2 01:00 GMT+2 -> D1 23:00 GMT > D2 03:00 GMT+3 -> D2 00:00 GMT > D2 04:00 GMT+3 -> D2 01:00 GMT This example is a little misleading. I don't think the values on the right should have GMT. The values on the left are instants (i.e. values relative to UTC epoch), but the values on the right are zoneless. In order to convert an instant to a zoneless timestamp, we need to know a time zone, and the DST offset of that time zone at that instant. That question, e.g. "What is the DST offset of the 'America/Los Angeles' time zone at instant D2 01:00 GMT" is well-defined. In order to convert a zoneless timestamp to an instant, we need to know a time zone, and the DST offset of that time zone at that zoneless timestamp. That question is not well-defined - it usually has 1 answer, but it might have 0 in the spring or 2 in the fall. For example, "What is the DST offset of the 'America/Los Angeles' time zone at timestamp '2020-11-01 01:30'" has answers +7 and +8, because that local time occurred twice. The same question in a time zone that does not shift (e.g. PST or PDT) has only one answer. It is possible that Avatica falls into the ambiguity of the second case. In which case, you should be able to come up with a test case against a reputable database (e.g. PostgreSQL, hsqldb) where Avatica's behavior differs. This matter is so complex that I no longer trust my own (or anyone else's) reasoning. Julian On Sun, Mar 28, 2021 at 2:44 AM Alessandro Solimando <[email protected]> wrote: > > Hi Vladimir, > your analysis seems correct to me, as well as your proposed solution. > > Best regards, > Alessandro > > On Sun, 28 Mar 2021 at 09:51, Vladimir Ozerov <[email protected]> wrote: > > > Hi, > > > > Avatica assumes that the underlying engine operates on relative time > > without time zone as opposed to global time (UTC). When you set a temporal > > value (e.g., a prepared statement parameter), Avatica adds the current > > offset to the passed time. When you read a temporal value, Avatica > > subtracts the current offset. This may lead to incorrect results if DST > > offset changes. > > > > Consider that we have a timezone with DST, that works as follows. D1 and D2 > > are two consecutive days (e.g., 24 and 25 Oct): > > D2 00:00 GMT+2 -> D1 22:00 GMT > > D2 01:00 GMT+2 -> D1 23:00 GMT > > D2 03:00 GMT+3 -> D2 00:00 GMT > > D2 04:00 GMT+3 -> D2 01:00 GMT > > > > Now consider, that we want to save D2 00:00 GMT+2 using Avatica. On write, > > Avatica will advance the time by the TZ offset. On read, Avatica will > > subtract the TZ offset. The problem is that different offsets will be used, > > leading to the incorrect result. The associated logic is located in > > AbstractCursor and TypedValue classes. > > > > long initial = [D2 00:00 GMT+2].epochMillis() // D1 22:00 GMT > > long onWrite = initial + offsetAt(initial); // D2 00:00 GMT > > long onRead = onWrite - offsetAt(onWrite); // D1 21:00 GMT > > assert initial == onRead; // Fails > > > > The fundamental problem is that the current time offset is used, which > > might differ before and after adjustment. One potential solution is to > > enhance the reading part. It should check whether the offset after the > > subtraction is the same and if not - do the additional adjustment to > > restore the proper time. > > > > Do you have any objections to the proposed change? > > > > Regards, > > Vladimir. > >
