Bug Hunting Session
Bug 125878 - Dates imported incorrectly from dbf file
Summary: Dates imported incorrectly from dbf file
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.4.0 target:6.3.0.1 target:6.2.5
Keywords:
Depends on:
Blocks:
 
Reported: 2019-06-12 10:51 UTC by lpr
Modified: 2019-06-15 12:00 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample DBF file (10.84 KB, application/x-dbase)
2019-06-12 10:53 UTC, lpr
Details

Note You need to log in before you can comment on or make changes to this bug.
Description lpr 2019-06-12 10:51:02 UTC
Description:
When I open a dbf file (foxpro), date appears to be incorrect.

For example:
01/06/2019 17:05:03 becomes 02/06/2019 10:05:02 and
02/06/2019 21:27:33 becomes 03/06/2019 18:27:32

You can try replicating the issue using this file (please change the extension from xls to dbf, this is the only way I could upload this samle file, as dbf format is not allowed)

https://ask.libreoffice.org/upfiles/15602705975313146.xls 

And this file is for comparison reasons only, which has the correct date. This file is exported using "DBF Viewer Plus" - and I can confirm that date/time is accurate.

Steps to Reproduce:
1. Open a dbf file (foxpro)
2. Check the date/time
3. You will notice that they are incorrect

Actual Results:
02/06/2019 10:05:02

Expected Results:
01/06/2019 17:05:03


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Please note that dbf file is a FoxPro file. Sample files can be found on the forum: https://ask.libreoffice.org/en/question/197142/incorrect-date-when-opening-a-dbf-foxpro-file/

I have tried this on Win10 as well as MacOS Mojave - results are identical.

NOTE: I will try to attach original dbf file to this report, but I'm not sure if it will be allowed. I will try once the bug is filed.
Comment 1 lpr 2019-06-12 10:53:36 UTC
Created attachment 152131 [details]
Sample DBF file

Sample DBF file - if you try opening using DBF Viewer Plus (http://www.alexnolan.net/software/dbf.htm) you will see the correct date/time.
Comment 2 Drew Jensen 2019-06-12 12:45:24 UTC
(In reply to lpr from comment #1)
> Created attachment 152131 [details]
> Sample DBF file
> 
> Sample DBF file - if you try opening using DBF Viewer Plus
> (http://www.alexnolan.net/software/dbf.htm) you will see the correct
> date/time.

I can duplicate the results using the default settings for LibeOffice.

If I go to Tools->Options and under LibreOffice Calc->Calculations change the date setting to 1/1/1904 then the date part of the field changes to what was expected. 

However the time portion does not change.
Comment 3 Eike Rathke 2019-06-12 13:17:05 UTC
Connecting/opening the file in Base yields the same results, which makes sense as Calc .dbf import uses Base functionality.

For the first record we have in connectivity/source/commontools/FValue.cxx connectivity::ORowSetValue::getDateTime() for case DataType::TIMESTAMP a css::util::DateTime aValue with

{NanoSeconds = 0, Seconds = 2, Minutes = 1025, Hours = 17, Day = 1, Month = 6, Year = 2019, IsUTC = 0 '\000'}

from which following the Minutes = 1025 of course leads to a later date+time.

Didn't investigate why the Base routines obtain that value.

Odd enough is that 1025 minutes are exactly 17 ours and 5 minutes, 17:05, the expected time.
Comment 4 Julien Nabet 2019-06-12 13:37:53 UTC
Taking a look to http://www.independent-software.com/dbase-dbf-dbt-file-format.html, I used online hexa editor and applied high end first:
for date: 0025840C -> 2458636
Using 2458636 in https://aa.usno.navy.mil/data/docs/JulianDate.php gives:
First June of 2019

for time: 03AA7618 -> 61503000
Using algo from the first page quoted indeed gives:
17h05m03

Perhaps we should check lcl_CalcJulDate and lcl_CalDate from connectivity/source/drivers/dbase/DTable.cxx
Comment 5 Julien Nabet 2019-06-12 13:57:57 UTC
The algo to convert Julian to Gregorian date seems corresponding to http://mathforum.org/library/drmath/view/51907.html

lpr: since first bVisual FoxPro or just
dBase is for dBase files which has a long story (see https://en.wikipedia.org/wiki/DBase) so the file format has changed with the time + those who use and change it to improve it.
Since the first byte of the attached file is 0x30, it seems indeed a file generated by Visual Fox pro (see https://www.dbf2002.com/dbf-file-format.html).


As you may have seen, depending the source, algo to convert Julian to Gregorian date is different.

In your link, you indicated:
"There is a free tool called DBF Viewer Plus, which is great, and this is the tool I used for exporting DBF to xlsx. I know the date displayed by this tool is correct because it is inline with the data where it originates from. "

Do you have Visual FoxPro to confirm the date? Indeed, perhaps DBF Viewer Plus may be buggy.
Comment 6 Eike Rathke 2019-06-12 14:08:06 UTC
Found the culprit, this has always been wrong..
Comment 7 Julien Nabet 2019-06-12 14:11:28 UTC
This patch fixes the time part:
diff --git a/connectivity/source/drivers/dbase/DTable.cxx b/connectivity/source/drivers/dbase/DTable.cxx
index f5dd3b61fcd2..a3025531c912 100644
--- a/connectivity/source/drivers/dbase/DTable.cxx
+++ b/connectivity/source/drivers/dbase/DTable.cxx
@@ -183,11 +183,9 @@ void lcl_CalDate(sal_Int32 _nJulianDate,sal_Int32 _nJulianTime,css::util::DateTi
     if ( _nJulianTime )
     {
         double d_s = _nJulianTime / 1000.0;
-        double d_m = d_s / 60.0;
-        double d_h  = d_m / 60.0;
-        _rDateTime.Hours = static_cast<sal_uInt16>(d_h);
-        _rDateTime.Minutes = static_cast<sal_uInt16>(d_m);
-        _rDateTime.Seconds = static_cast<sal_uInt16>(( d_m - static_cast<double>(_rDateTime.Minutes) ) * 60.0);
+        _rDateTime.Hours = static_cast<sal_uInt16>(d_s / 3600.0);
+        _rDateTime.Minutes = static_cast<sal_uInt16>( (d_s - (_rDateTime.Hours * 3600)) / 60 );
+        _rDateTime.Seconds = static_cast<sal_uInt16>(d_s - (_rDateTime.Hours * 3600) - (_rDateTime.Minutes * 60));
     }
 }

(I'm pretty sure it can be optimized).
Comment 8 Eike Rathke 2019-06-12 15:24:38 UTC
I have it already at gerrit https://gerrit.libreoffice.org/73893
Comment 9 Commit Notification 2019-06-12 20:50:36 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/1436d2307d90f5ddbdcb0f129e59544306530342%5E%21

Resolves: tdf#125878 correct calculation of H:M:S from timestamp

It will be available in 6.4.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 10 Eike Rathke 2019-06-12 20:53:35 UTC
Pending review
https://gerrit.libreoffice.org/73914 for 6-3
https://gerrit.libreoffice.org/73915 for 6-2
Comment 11 Commit Notification 2019-06-13 10:32:40 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/9fbe6186b1c42ed775a64b666203979285e379fb%5E%21

Resolves: tdf#125878 correct calculation of H:M:S from timestamp

It will be available in 6.3.0.1.

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

Affected users are encouraged to test the fix and report feedback.
Comment 12 lpr 2019-06-13 10:36:58 UTC
(In reply to Julien Nabet from comment #5)

> "
> 
> Do you have Visual FoxPro to confirm the date? Indeed, perhaps DBF Viewer
> Plus may be buggy.

I don't have Visual FoxPro but I can confirm that date and time displayed on DBF Viewer is correct, because this file is generated by a till system and I can verify date/time stamps with actual receipts.
Comment 13 Commit Notification 2019-06-14 12:16:40 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/5daf494782505dbee27b97a43a79588106715b16%5E%21

Resolves: tdf#125878 correct calculation of H:M:S from timestamp

It will be available in 6.2.5.

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

Affected users are encouraged to test the fix and report feedback.