The migration of date and time values from an HSQLdb data source to a Firebird data source requires a decision with regards to how to handle the difference between the two data engines interpretation of these values. The HSQL RDBMS (servers and client tools) work with an awareness of the users timezone and use to transform data sent from the client to the server and again from the server to the client. Transforming the data from client_TZ -> UTC(0) when sent and from UTC(0) -> Client_TZ when retrieved. The Firebird RDBMS (servers and client tools) have no such awareness of time zones, writing whatever the client gives to the server. Since the migration assistant in LibreOffie is reading directly from the disc image for the the HSQL data store, with no use of HSQL tools, the value retrieved is always the value relative to the UTC(0) timezone. If that value is written as is into the new Firebird data source the users will see a difference in their data with displayed dates and times adjusting. This change currently happens automatically. The RFE is to add during the migration assistant function a dialog which gives the users the option to move the date as stored or to have the assistant transform the values from the UTC(0)_TZ value to the users Timezone equivalent and therefore maintaining the same values presented to the user on screen and in reports. For reference see this issue: Firebird: Migration: Time values are being changed during migration process (data type TIME and DATETIME) https://bugs.documentfoundation.org/show_bug.cgi?id=117732
Seems a fair enough request to me. Confirming.
[1] says that Firebird 4.0 Beta1 will have TIME WITH TIMEZONE and DATETIME WITH TIMEZONE. Firebird 4.0 is still alpha, and I have not found a schedule for its advancement from that state. The latest "daily" build of 4.0, [2], was on 2018-11-25, which is earlier than the commit in question. I wonder whether this promise from Firebird should change our plans with respect to this bug and bug 117732? In the long run, I expect that we can most nearly maintain the behaviour that users are familiar with by: (1) Our conversion from HSQLDB converts to fields with-timezone-GMT. (2) We translate data retrieved from the database to local time and we convert data writtem to the database to GMT. Do we expect to do that? Should this change our plans with respect to always offering the conversion from embedded HSQLDB to embedded Firebird? Of course, after doing (2), it will be tempting to give the user access to additional functionality by optionally--or, optionally if the .odb was converted from embedded HSQLDB--giving the user some explicit access to timezone data. I do not know what this might look like. Do we have anything like this in our connection to a database already offering with-timezone? references ---------- [1] <http://tracker.firebirdsql.org/browse/CORE-694?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel> [2] <http://web.firebirds1l.org/download/snapshot_builds/linux/fbtrunk>
Responding to comment 2 ... If I am reading <https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.time_zone.md> (commit 330650c4, 2018-07-18) correctly, behaviour (2) may "just work".
Take a look at Lionel's opening comments in bug 67763 under "Timezone"
*** Bug 126408 has been marked as a duplicate of this bug. ***
The small database in attachment 135786 [details] (originally created for bug 112023) may be used as a test case. These data were created in the CET timezone. Upon migration, the TIMESTAMP data are moved 1 hour backwards in time. (At the same time, migration completely corrupts the DOUBLE data in the base, which I have further commented in bug 126268.) These problems are still present in LO version 6.3.1.2
Update to my previous comment: The migration of both TIMESTAMP and DOUBLE works in the daily build of 6.4.0.0.alpha0+ Furthermore, in 6.3.2.1 (RC1 version of upcoming 6.3.2), migration is suggested only if experimental features are chosen, so (unlike 6.3.0 and 6.3.1) normal users will not experience the bug. Thus, the bug appears resolved.