[Libreoffice-bugs] [Bug 117732] Firebird: Migration: Time values are being changed during migration process (data type TIME and DATETIME)

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=117732

--- Comment #21 from Terrence Enger <lo_bugs at iseries-guru.com> ---
Drew Jensen wrote in comment 10:

> As the other issue points out a feature of HSQLdb is to store time
> values as the UTC+0 time and accounts (changes) the time
> representation supplied from the user based on the users local time
> zone, later when it supplies a time representation for the value it
> again accounts for the users local time zone - users in different
> time zones see the 'time' in their time zone, the value stored never
> changed.
>
> Firebird doesn't know how to do that at the moment. Users in
> different time zones reading the value will see identical time
> representations but they are one their own to deal with the fact the
> time they are seeing may be from the perspective of a different time
> zone from their time zone. (note; from scanning their bug/ticket
> tracker

The planning document <https://www.firebirdsql.org/en/planning-board/>
references CORE-694 <http://tracker.firebirdsql.org/browse/CORE-694>
and CORE-909 <http://tracker.firebirdsql.org/browse/CORE-909>.

> it looks like that (support time zones) is in the works and
> possibly in one of the minor 3.x releases, but I didn't drill down
> to see if that means going to a common storage tz (UTC+0).

The first README for the time zone feature, "Time Zone support (FB
4.0)",
<https://github.com/FirebirdSQL/firebird/blob/work/time-zone-support/doc/sql.extensions/README.time_zone.md>
is quite new (May, 2018), so it might be right.

The first paragraph says:

    Time zone support consists of TIME WITH TIME ZONE and TIMESTAMP
    WITH TIME ZONE data types, expressions and statements to work with
    time zones and conversion between data types without/with time
    zones.

and under the heading "SET TIME ZONE BIND statement":

    Old clients may not understand the new data types, so it's
    possible to define the bind to LEGACY and the expressions will be
    returned as TIME WITHOUT TIME ZONE and TIMESTAMP WITHOUT TIME
    ZONE, with appropriate conversion.

    The bind configuration is also applicable to input parameters.

The planning document <https://www.firebirdsql.org/en/roadmap/>
classifies time zone support as an optional feature of FB 4.0: it may
be postponed if its development does not fit the release schedule.
"Beta Release (stable enough)" of FB 4.0 was 2018-05-01, but time zone
support is still "in progress".  I see no planned date for release for
use in production.

Whether LibreOffice will move to Firebird 4.0, and when, and whether
time zone support will make it into FB 4.0, are questions far above my
pay grade.


> IDK what the right answer is here - the only way to not loose the
> old data is to move the UTC+0 value into the firebird table,

Agreed.  Especially surprising would be DATE changed by a migration
executed west of GMT.

I think it is of the utmost importance that we not *surprise* users.

> but then the display will change for all users not in the UTC+0 time
> zone.

One can imagine translating times and timestamps between GMT in the
table and local time in the UI.  This would, IIUC, duplicate the
visible behaviour of embedded HSQL for TIME and TIMESTAMP and improve
on the behaviour for DATE fields.

This is what future FB will do after we execute the statement "SET
TIME ZONE BIND LEGACY".  How many places in LibreOffice code would
have to change to do this ourselves, I wonder?

Of course, any user who issues direct SQL gets whatever she gets.  But
a view may be defined using direct SQL, so this is another opportunity
to issue a warning in advance of an unpleasant surprise.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180705/02fe7d94/attachment.html>