I use the MySQL 8.0.18 with its own JDBC connector connecting the LibreOffice Base 6.3.3.2 to it. I have no idea which dates exactly but somewhat dates with month between 04…10 are actually set to one day before the entered date.
I’ve read the LibreOffice Base displays MySQL date fields off by one day article but I have synchnized the server timezone through the default_time_zone setting in the my.ini file with my local time (“SELECT @@global.time_zone” check returns correct timezone “+02:00” I set there). Also I found a suggestion to try to use the method described here Base - Connect MySQL JDBC TimeZone error but when I click “database properties” there is no “Datasource URL” but several separated settings there only, i.e. host, database name, … and there is no place to add connection URL parameters like “useLegacyDatetimeCode”.
For example which dates become one day before which not - 2017-11-20 remains as is when set and 2017-09-20 becomes 2017-09-19 once set. If I check the database it has set the date to the corrected by LO (i.e. one day behind if modified).
Also I can add that I have set the ukrainian locale in the LibreOffice and date I enter and see is actually in the DD.MM.YY format (e.g. “20.09.17”). Date type is “DATE” for this field in MySQL.
Any ideas?
How do you enter dates? E.g. import from file, Base input form, SQL update query directly on the server
Do you mix DATE and DATETIME data anywhere in your queries or expressions? Rounding errors in internal calculations may occur, which could explain the seeming randomness.
Is Microsoft Excel or other titles involved in any manner? By using the application, or import from files of Excel format. Excel carries over (intentionally) the Visicalc bug where 1900 is counted as a leap year. This makes some dates off by one day. That specific mistake does not explain the errors described here, but there may be other glitches at play.
I have this same problem, and I have looked at the same articles mentioned by VirtualVAT. In my case, I gave up trying to fix it, so I manually re-entered all of the dates in my database by opening the table in Table Data View and going through each row and changing the dates. A few days later, I opened the database, and all dates with a month between 04 and 10 had changed to (once again) be off by one day before the entered date. For example, if the date I had entered into the database was 6/10/2023, the date was now showing as 6/9/2023. I am running LibreOffice Base 7.5.2.2 on Windows 365 (X86_64). All dates were entered manually via a Base form; they were not imported or copied and pasted from Excel or anywhere else.
I am now going to manually correct all of the dates (again, by opening the database in Table Data View) and try using the database on my Linux box to see if the problem recurs or not.
Hi, RobertG, thanks for the response. I’m not sure what kind of database it is. How do I tell that? I tried going to the Edit menu, then Database, but Properties and Connection Type are grayed out.
@forliberty Might be you are running an internal database. This thread is about a special (buggy?) behavior of MySQL-connections. Have a look at the bottom of the main Base window. “Embedded database” might appear there. Also the type of the database will appear: HSQLDB? Firebird?
Open a new thread for the behavior you detected in your embedded database.
Thanks again, RobertG. You are correct, in the main database window it shows at the bottom that this is a HSQLDB Embedded database. I will open a new thread in a few days - although since my symptoms are the same as VirtualVAT’s, this may be a broader issue with Base. I’m running some troubleshooting on my Linux box, so hopefully I will have some more data before I start a new thread.