Description: When importing dates from a Calc test table to a Base Firebird database table, some dates are imported erroneously and furthermore are shown in differently depend on the way of view Steps to Reproduce: To perform this test, a Firebird database has been created in an external file. This type of database has been chosen because of the ease of creating it and accessing the data directly, without the need of a Base database. Although it is needed a Firebird server in order to know what data has actually been saved in the database. -- Step 1. Creation of the database. ------------------------------------------- 1. Open Base 2. In the Database Wizard, select Connect to an existing database > Firebird External and click Next. 3. In step 2 of the wizard, Connection Settings, click the Create New button 4. In the Save As dialog, browse for a suitable location and name the FDB file in the Firebird database, for example DATES1.FDB and click the Save button 5. When you return to the Database Wizard, click the Next button 6. In step 3 of the wizard, Set up user authentication, leave everything blank, as is, and click the Next button 7. In step 4, leave the default options for Base to register the database, and then open the file. Click Finish 8. In the Save As dialog, search for a suitable location and save the database with the name Dates1.odb 9. The database will be created and opened -- Step 2. Import data from Calc to a Firebird table ----------------------- 1. With the database open, as we left it in the previous step, open the Calc Dates.ods file with the sample data 2. On Sheet1 containing the data, select all (Edit > Select All) 3. Select Edit > Copy menu (or Ctrl+C) 4. Switch to Base, and in the Tables section, right-click and select Paste from the context menu. 5. In the Copy Table dialog that appears, name the new table, for example Table1. Under Options, select Definition and data and check the Use the first line as column names box. and click Next. 6. In the next step, Apply columns, move the three columns to the right side. and click Next. 7. In the next step: a) Column ID, assign Field type: INTEGER, and do it Primary key (right-click over ID and select) b) Column TEXT, assign Field type VARCHAR and Length 12 c) Column DATE, assing Field type DATE 8. Click Create 9. A warning message appears “firebird_sdbc error: value exceeds the range for valid dates caused by 'isc_dsql_execute' Continue any way?”. Click Yes 10. Table1 has been created -- Step 3. View table data ------------------------------------- To view the table data: 1. Double-click on the name of the newly created table 2. To see the dates in the same format as they had in the Calc table, right-click on the header of the DATE column and in the context menu select Column Format 3. In the Field Format dialog, on the Format tab, under Category, select Date, and under Format, select 31/12/1999. Click OK. 4. Observe the data. 5. Fields with ID = 1 and ID = 2 have erroneous dates 6. Field with ID = 3 has not been imported (maybe the warning message in the step 9 above) -- Step 4. View data in SQL command dialog -------------------- 1. In Base menu, select Tools > SQL 2. In the box Command to execute write Select * from “Table1” 3. Check Run SQL command directly 4. Check Show output of “select” statements 5. Click on Execute button 6. Observe the results on Output box --Step 5. View data directly in Firebird ------------------------- 1. On an operating system console run isql or isql-fb as needed 2. Connect to the database: CONNECT /path/to/folder/DATES1.FDB; 3. Show the data in the table Table1: SELECT * FROM "Table1"; 4. Observe the data. Actual Results: - Some dates are imported in an incorrect way - Depends on the way of view the data, the dates showed are different - Stored data are incorrect and are showed incorrectly Expected Results: The imported dates should be the same in any way of view and the stored data should be the same that the original one 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 183673 [details] Image comparing the different data obtained
Created attachment 183674 [details] Original Calc file used to test
Created attachment 183675 [details] Firebird database file created in the test
Created attachment 183676 [details] Base file created in the 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.
Not being a Base person I didn't try, but I assume that the display part of the table grid view "Dates in table view" is fixed with the change for bug 152114. Seeing the screenshots "Dates in output of a SQL command" and "Data stored in the database" it looks like dates are stored using the proleptic Gregorian calendar, i.e. there is 1582-10-14 instead of the Julian date 1582-10-04, which makes sense. Hence the Julian dates 0001-01-02 and 0001-01-01 are clamped to the proleptic Gregorian date 0001-01-01 as otherwise a negative date would have to be stored, which probably is not possible. If so, there's nothing to fix other than maybe invalidating/omitting values/records. If someone familiar with Base and Firebird could investigate and confirm.. Also, please try with a build including the fix for bug 152114 whether the ID = 3 case 03/01/0001 is fixed, it should be (a correct) 0001-01-01 as the proleptic Gregorian calendar there is 2 days behind the Julian calendar.
This is what I see with Version: 7.5.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 1435c5b12646269e2b5b58ec7d51626dce6505db CPU threads: 8; OS: Mac OS X 13.0; UI render: default; VCL: osx Locale: fr-FR (fr_FR.UTF-8); UI: en-US Calc: threaded assuming that the fix for bug 152114 has made it into the latest daily build ?
Created attachment 183731 [details] Screenshot of table display in daily dev 23/111/2022
Created attachment 183734 [details] screenshot of table in recent 7.5 build (In reply to Alex Thurgood from comment #7) > assuming that the fix for bug 152114 has made it into the latest daily build > ? Yes, the build you used does include it. (In reply to jcsanz from comment #0) > -- Step 2. Import data from Calc to a Firebird table ----------------------- > [...] > 8. Click Create > 9. A warning message appears “firebird_sdbc error: value exceeds the > range for valid dates caused by 'isc_dsql_execute' Continue any way?”. Click > Yes I don't see the warning, and the date with ID 3 has been imported properly. Output of SQL command: 1,01/01/0001,0001-01-01, 2,02/01/0001,0001-01-01, 3,03/01/0001,0001-01-01, 4,04/01/0001,0001-01-02, 5,04/10/1582,1582-10-14, 6,15/10/1582,1582-10-15, 7,09/09/9999,9999-09-09, 8,31/12/9999,9999-12-31, Version: 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
I also couldn't believe and tried myself. If I follow your steps 1-3 and import pasted data, the database table view does have record ID 3 as anticipated, displaying 0001-01-03 correctly, the same resulting 0001-01-03 for IDs 1 and 2 (as explained stored is 0001-01-01 proleptic Gregorian calendar date for all three). For step 4 note that the instructions contain typographic quotes, instead it should be Select * from "Table1" The result output is as expected and as pasted by Stéphane. For step 5 unfortunately my isql-fb supports only on-disk structure 13.0 but not 12.0, but I'd expect the result to be the same.
Created attachment 183740 [details] Different views of the dates after import Original Calc dates are different of the dates in Base after importing and both are different than the stored dates. I suppose that the user expects that the dates (all the data) are the same in Calc, in Base and in the stored database, whichever the calendar was used. I was supposed to use proleptic Gregorian calendar in Calc, so it is not needed any adaptation to export data to (a proleptic Gregorian calendar in) Base and also is not needed any other modification to save data on database. But if I were using a Julian calendar or a mix of calendars also it is not needed any adaptation of dates, (it is supposed) I was aware what I'm doing.
The SQL statement outputs the raw data formatted as date, which is stored in the proleptic Gregorian calendar, and apparently dates before 0001-01-01 (i.e. negative dates or dates BCE) can not be stored in the database (if they could in Firebird then someone would have to implement that). The table grid view displays data in the locale's calendar. > I was supposed to use proleptic Gregorian calendar in Calc Calc does not use the proleptic Gregorian calendar. It uses the locale's default calendar. As explained in bug 152114 that usually is (does not have to be) the Gregorian calendar after the Gregorian cut-off date, and Julian calendar before that. If the cut-off date is 1582-10-15 (Gregorian) then subtracting 1 from that date yields 1582-10-04 (Julian). Btw, also for Calc .ods and other ODF file formats, dates are always stored in the proleptic Gregorian calendar. To solve this (expectation of display being what's stored) it would need an option to force the proleptic Gregorian calendar for display.
(In reply to Eike Rathke from comment #12) > The SQL statement outputs the raw data formatted as date, which is stored in > the proleptic Gregorian calendar, and apparently dates before 0001-01-01 > (i.e. negative dates or dates BCE) can not be stored in the database (if > they could in Firebird then someone would have to implement that). The table > grid view displays data in the locale's calendar. > > > I was supposed to use proleptic Gregorian calendar in Calc > Calc does not use the proleptic Gregorian calendar. It uses the locale's > default calendar. As explained in bug 152114 that usually is (does not have > to be) the Gregorian calendar after the Gregorian cut-off date, and Julian > calendar before that. If the cut-off date is 1582-10-15 (Gregorian) then > subtracting 1 from that date yields 1582-10-04 (Julian). Btw, also for Calc > .ods and other ODF file formats, dates are always stored in the proleptic > Gregorian calendar. > Now the light has come on and I'm beginning to understand all this mess.... But in any case, it seems to me that most users don't know about calendars and don't care. I myself was unaware of it and, as you can see, I'm still confused. But me, and I think most of the users, expect the same data in every place whatever the calendar is, and whatever was the application that stored the data. On the other hand, I think it makes no sense that three dates that are different in the source (0001-01-01, 0001-01-02 and 0001-01-03) are all stored as the same date (0001-01-01) instead of showing an error in case the conversion produces invalid dates for the database. > To solve this (expectation of display being what's stored) it would need an > option to force the proleptic Gregorian calendar for display. That could be a solution, especially in Base, since most databases, it seems, use the Gregorian proleptic calendar
So, given that after the correction of tdf#152114 the errors in the dates import are solved and that the results (which I thought were wrong) are correct and according to what was expected, this bug should be closed as solved, shouldn't it? Who does it? Eike? myself? (In reply to Eike Rathke from comment #12) > > To solve this (expectation of display being what's stored) it would need an > option to force the proleptic Gregorian calendar for display. I will surely make an enhancement request asking for it. Thank you very much for your patience and quick resolution.
Marking as fixed by: https://git.libreoffice.org/core/commit/0e2781bb5fbf7035278e311f8b50a2955048b14c and https://git.libreoffice.org/core/commit/52ff16771ac160d27fd7beb78a4cfba22ad84f06 Thank you both!