Description: When importing dates from a Calc test table to a Writer database fields, some dates shown in Writer are different from the original dates in Calc Steps to Reproduce: To perform this test, it is used the sample Calc file attached. 1. Open a new Writer document 2. From the menu, select Insert > Field > More Fields (or press Ctrl+F2) 3. In the Fields dialog that appears, select the Database tab 4. In Add Database File, click the Browse button 5. Navigate to select the Calc file with the test data and click on the Open button. 6. In the upper-right list Database selection, a new option will be added with the name Dates 7. Click on the + sign to display the new Dates option and select the name of the sheet containing the data (Sheet1) 8. In the list on the left Data, select, for example, Database Name and click the Insert button. This step is used to register the database, so it does not matter which option you select in Data, whenever a field is inserted. 9. Close the Fields dialog 10. Show data sources with View > Data Sources menu 11. In the tree on the left side of the data sources, display Dates > Tables > Sheet1. On the right side you will see the table with the data coming from the Calc sheet. 12. To see the data better, in the data table on the right, right-click on the header of the DATE column and in the contextual menu select Column Format 13. In the Field Format dialog box, in the Category list, select Date, and in the Format list, select 31/12/1999. Click the OK button. This will display the date in the same format as on the original Calc sheet. 14. Observe the differences with the original data Actual Results: Some dates imported are different from original dates in Calc Expected Results: Same dates after importin Reproducible: Always User Profile Reset: No Additional Info: Version: 7.4.2.3 (x64) / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL Version: 7.4.2.3 / LibreOffice Community Build ID: 40(Build:3) CPU threads: 1; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb) Locale: es-ES (es_ES.UTF-8); UI: es-ES 7.4.2-2 Calc: threaded
Created attachment 183672 [details] Calc file with the dates used to test
I have done the same test in a MariaDB database with a Direct connection and the results obtained are exactly the same but in MariaDB the record with ID = 3 has been imported, but the date showed in the table view is "03/01/0001" and the date actually stored is 31/12/0000. Note that the las date is not a valid date in Firebird, so it couldn't be imported in that database.
Sorry, the last comment is not to this bug
Created attachment 183677 [details] Sample with range dates Seems it happens with dates before 03/01/0001. Version: 7.5.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: a81e957f5026373f3935390c786c21416fc74fcc CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: CL threaded
I can confirm what you see, but isn't the first lines of date data in your Calc sheet an invalid date anyway ? Attaching screenshot of what I see with Version: 7.3.7.2 / LibreOffice Community Build ID: e114eadc50a9ff8d8c8a0567d6da8f454beeb84f CPU threads: 8; OS: Mac OS X 13.0; UI render: default; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR Calc: threaded from the Apple AppStore (Arm Silicon M1).
Created attachment 183689 [details] Screenshot comparing date display in Calc and Writer
@Eike : thoughts ?
(In reply to Alex Thurgood from comment #5) > I can confirm what you see, but isn't the first lines of date data in your > Calc sheet an invalid date anyway ? I don't think so. The first date is one day before 0001-01-01 (using Gregorian proleptic calendar) and is a date that exists (this is 0001-01-01 BC).
(In reply to Alex Thurgood from comment #7) > @Eike : thoughts ? I think that every date written in some place should be the same in whatever other place, without any modification or adaptation. If I write a date, I expect that date remain unchanged. Maybe LibreOffice is trying to adapt dates to a specific calendar. But does LibreOffice know which calendar I am using when I type the date?
(In reply to jcsanz from comment #0) > 13. In the Field Format dialog box, in the Category list, select Date, > and in the Format list, select 31/12/1999. Click the OK button. This will > display the date in the same format as on the original Calc sheet. That of course depends on the current locale and everyone will see something different with the default date format, i.e. in an en-CA locale the default format is YYYY-MM-DD Now to the symptom: registering the Calc sheet as database and displaying that converts content to a database view using formatted fields, with some back-and-forth conversion (serial date number offset from the null-date to date and back) involved that apparently has bugs with at least negative proleptic Gregorian dates. (In reply to jcsanz from comment #8) > The first date is one day before 0001-01-01 (using > Gregorian proleptic calendar) and is a date that exists (this is 0001-01-01 > BC). Actually 0001-12-31 BCE ... (In reply to jcsanz from comment #9) > Maybe LibreOffice is trying to adapt dates to a specific calendar. But does > LibreOffice know which calendar I am using when I type the date? Input happens in the locale's default calendar, usually Gregorian, but for dates before the Gregorian cut-off date (common is 1582-10-15 but depends on locale) it is the Julian calendar. Internally stored is the date serial number as day offset from the null-date, usually 1899-12-30 in LibreOffice. Calculations use the proleptic Gregorian calendar, as well as document storage.
(In reply to Eike Rathke from comment #10) > (In reply to jcsanz from comment #0) > > 13. In the Field Format dialog box, in the Category list, select Date, > > and in the Format list, select 31/12/1999. Click the OK button. This will > > display the date in the same format as on the original Calc sheet. > That of course depends on the current locale and everyone will see something > different with the default date format, i.e. in an en-CA locale the default > format is YYYY-MM-DD Of course, depend on the locale you can view the date in a different way, but the date should be the same > > Now to the symptom: registering the Calc sheet as database and displaying > that converts content to a database view using formatted fields, with some > back-and-forth conversion (serial date number offset from the null-date to > date and back) involved that apparently has bugs with at least negative > proleptic Gregorian dates. With negative dates and 0001-01-01 and 0001-01-02. And maybe other different dates see tdf#152118 > > Gregorian proleptic calendar) and is a date that exists (this is 0001-01-01 > > BC). > Actually 0001-12-31 BCE ... Yes, of course, it was a slip > Input happens in the locale's default calendar, usually Gregorian, but for > dates before the Gregorian cut-off date (common is 1582-10-15 but depends on > locale) it is the Julian calendar. Internally stored is the date serial > number as day offset from the null-date, usually 1899-12-30 in LibreOffice. > Calculations use the proleptic Gregorian calendar, as well as document > storage. Well, ok ... but... If I've been taught that America was discover on October 12th, 1472, what calendar was using my teacher? I don't know, but I expect that if write 1472-10-12 (or 12/10/1472) it remains the same in Calc, in Writer, in Base and in whatever place it was used. And I don't expect the system adapt my date to a different calendar when I move the date from one component to other (regardless of the calendar used by me or configured in the application). Any case, I think there is a bug when 0001-01-01 and 0001-01-02 are converted to a different date but 0001-01-03 is not converted
(In reply to jcsanz from comment #11) > (In reply to Eike Rathke from comment #10) > > that apparently has bugs with at least negative > > proleptic Gregorian dates. > > With negative dates and 0001-01-01 and 0001-01-02. Note that around BCE/CE the difference between Julian and proleptic Gregorian calendar is 2 days, the Julian 0001-01-01 and 0001-01-02 dates are already negative proleptic Gregorian calendar dates. > Well, ok ... but... If I've been taught that America was discover on October > 12th, 1472, what calendar was using my teacher? Certainly not a Gregorian calendar. > I don't know, but I expect > that if write 1472-10-12 (or 12/10/1472) it remains the same in Calc, in > Writer, in Base and in whatever place it was used. As said, there's a bug in the conversion to formatted field strings in the database view.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/0e2781bb5fbf7035278e311f8b50a2955048b14c Related: tdf#152114 Move some tools' Date class algorithms to comphelper::date It will be available in 7.5.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/52ff16771ac160d27fd7beb78a4cfba22ad84f06 Resolves: tdf#152114 Use comphelper::date algorithms It will be available in 7.5.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.
Created attachment 183737 [details] same results in data source (left) and original sheet (right) Following the steps, it is verified as fixed for: Version: 7.5.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 1435c5b12646269e2b5b58ec7d51626dce6505db CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Thanks Eike!
For the sake of completeness, issue was present in: Version: 6.3.6.2 Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3; Locale: en-AU (en_AU.UTF-8); UI-Language: en-US Calc: threaded
Created attachment 183739 [details] Results after bug correction All seems correct now, also with extended test dates. Thanks, Eike!