Bug 71007 - EDITING: Embedded Firebird - Impossible to set milliseconds in a timefield with GUI
Summary: EDITING: Embedded Firebird - Impossible to set milliseconds in a timefield wi...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.alpha0+ Master
Hardware: x86-64 (AMD64) Linux (All)
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2013-10-29 16:29 UTC by Robert Großkopf
Modified: 2018-01-04 12:22 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Start with LO 4.2 only - try to input time with milliseconds. Works only in direct SQL. (49.91 KB, application/vnd.oasis.opendocument.base)
2013-10-29 16:29 UTC, Robert Großkopf
Details
Firebird-3: Open the query and have a look (4.84 KB, application/vnd.oasis.opendocument.database)
2017-01-03 08:28 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2013-10-29 16:29:01 UTC
Created attachment 88306 [details]
Start with LO 4.2 only - try to input time with milliseconds. Works only in direct SQL.

Create an embedded Firebird-Database (see attachment).
Create a table with primary-key and a field of the type "TIME".
Format this field with the formatcode [HH]:MM:SS.0000 (Language English(USA)).
Try to put the following value in the TIME-field:
13:24:35.1234
After saving it is shown as
13:24:35.0000
Seems the milliseconds have been gone.
Same behavior when trying this with a form connected to this table.

Try the query of the attachment. There are two fields. One field shows the value of "Time" as time, the second field shows it, as it must be reformatted for showing the milliseconds in Firebird (CAST("Time" AS VARCHAR(13))).
The milliseconds had been gone.

Try the following in Tools → SQL:
UPDATE "Table_Time" SET "Time" = '12:24:56.1234' WHERE "ID" = 1;
(only if there is a field "ID2 with the value '1')
Refresh the query. The query will show the milliseconds in this fields.

This shows: 
Firebird is able to save the milliseconds.
The GUI isn't able to send the right values to Firebird.
The GUI isn't able to fetch the values from Firebird in a time-field or a formatted field.
Comment 1 Julien Nabet 2013-10-29 23:35:51 UTC
Comment on attachment 88306 [details]
Start with LO 4.2 only - try to input time with milliseconds. Works only in direct SQL.

Mimetype fixed
Comment 2 Alex Thurgood 2015-01-03 17:41:04 UTC Comment hidden (no-value)
Comment 3 Robert Großkopf 2017-01-03 08:24:31 UTC
Have tested this again.
There isn't any possibility to work with milliseconds and Firebird through the GUI of Base.
The GUI doesn't send milliseconds to a Timestamp-field or a Time-field. You could do this with direct SQL as described in the reported bug.
The GUI isn't able to show milliseconds, which are saved in a Timestamp-field or in a Time-field. This shows the attached query, where the milliseconds could be seen while casting the fields to varchar-fields.

This is a regression to HSQLDB. If you define in HSQLDB a Timestamp-field with direct SQL to 
ALTER TABLE "Table" ALTER COLUMN "Timestampfield" TIMESTAMP(6)
you could input milliseconds in the GUI and you could see milliseconds in the GUI with HSQLDB.
Comment 4 Robert Großkopf 2017-01-03 08:28:48 UTC
Created attachment 130106 [details]
Firebird-3: Open the query and have a look

Attachment is created with 
Version: 5.4.0.0.alpha0+
Build ID: 2a4cd80abcf9e515d1ce3b3a944b573bdc42bff2
CPU Threads: 4; OS Version: Linux 4.1; UI Render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2016-12-22_00:18:04
Locale: de-DE (de_DE.UTF-8); Calc: group

so a new Firebird-3-file. Open the query. The fields for timestamp and for time doesn't show the milliseconds, which are part of the fields as could seen when casting the fields to varchar-fields.
Comment 5 How can I remove my account? 2018-01-02 15:01:05 UTC
Unfortunately the public Firebird API does not provide a way to encode/decode an ISC_TIME or ISC_TIMESTAMP passing also fractions of seconds. But as we know that ISC_TIME is simply in units of seconds divided by ISC_TIME_SECONDS_PRECISION (seconds/10000), it is trivial to manually handle the fractional seconds. Suggested fix in https://gerrit.libreoffice.org/#/c/47266/
Comment 6 Commit Notification 2018-01-02 19:10:21 UTC
Tor Lillqvist committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e0a22d47d281f61f51ead6d2831cd53c15036ffe

tdf#71007: Pass also fractional seconds to/from Firebird

It will be available in 6.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 7 Alex Thurgood 2018-01-04 12:18:02 UTC
Testing with 

Version: 6.1.0.0.alpha0+
Build ID: 5b7ec49f453c3732e2482b26bce4243eb1a10f40
CPU threads: 4; OS: Mac OS X 10.13.2; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group threaded

appears to show that this now works as desired (I can see the fractions of seconds in the GUI with Robert's example db).

Setting as RESOLVED/VERIFIED FIXED

@Tor : any chance of a backport ?