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.
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.
(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.
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.
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
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.
Found the culprit, this has always been wrong..
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).
I have it already at gerrit https://gerrit.libreoffice.org/73893
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.
Pending review https://gerrit.libreoffice.org/73914 for 6-3 https://gerrit.libreoffice.org/73915 for 6-2
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.
(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.
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.
Created attachment 155729 [details] need to change date
(In reply to Kishore from comment #14) > Created attachment 155729 [details] > need to change date Please submit a new bugtracker with a bit more description.