Bug 119675 - Firebird: Migration: User dialog to set treatment of datetime and time values during migration
Summary: Firebird: Migration: User dialog to set treatment of datetime and time values...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.0.0.alpha0+
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-09-03 20:21 UTC by Drew Jensen
Modified: 2019-01-22 09:17 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Drew Jensen 2018-09-03 20:21:52 UTC
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
Comment 1 Alex Thurgood 2018-09-05 07:28:01 UTC
Seems a fair enough request to me. Confirming.
Comment 2 Terrence Enger 2018-12-11 19:12:10 UTC
[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>
Comment 3 Terrence Enger 2018-12-12 21:00:55 UTC
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".
Comment 4 Alex Thurgood 2019-01-22 09:17:13 UTC
Take a look at Lionel's opening comments in bug 67763 under "Timezone"