Dates off by one day - LibreOffice Base - HSQLDB embedded database - Windows & Linux

[forliberty]

I am seeing some strange behavior with dates in Base when transferring the database from Linux to Windows and then back again. I created the database on a Linux box (Zorin OS 15) several years ago and have added to it on a succession of Linux machines over the years, with no issues. Recently, I had to make some additions to the database from a Windows machine. However, in opening the database on the Windows machine, many (but not all) of the dates were off by one day. After a lot of troubleshooting, I finally decided to empty my database of data and then add some dummy date, first on the Linux machine and then on the Windows machine. Here is what I found.

I started with the Linux machine, adding records with various dates. Here is what my table looked like:

2. I then moved the database to the Windows machine. I noticed that all dates in the months of January, February, November, and December were fine. BUT dates from April 1 to October were off by one day; the Windows machine displayed them as one day earlier (e.g., 4/1/20 became 3/31/20).

Here is a screenshot of how the table looked on Windows:

I then added new records to the database from the Windows machine, using the same dates that I had originally entered on the Linux machine. (So now I had two identical entries in the database - one made on the Windows machine and one made on the Linux machine.) So now my table looked like this:

I then moved the database back to my Linux machine, and it looked the same as it did on the Windows machine. In other words, by opening the database on Windows, the database was permanently damaged in that the dates I had entered in Linux from April 1 through October were now off by one day.

My troubleshooting with dummy data does not explain all of the anomalies I was seeing with my real database. For example, when another user with a different Windows machine entered data into the database, all of the dates she entered were off by one day when I viewed the database on my Linux machine. (By contrast, in my test case, the dates I entered on the Windows machine displayed correctly on my Linux machine.) Also, when I viewed my real database on my Windows machine, the dates that were off did not always fit into the April 1 through October paradigm (e.g., one record with a date of April 1 displayed correctly on both the Windows and Linux machines). But at least my troubleshooting demonstrated some repeatable anomalous behavior.

I have reviewed the following: https://ask.libreoffice.org/t/base-connect-mysql-jdbc-timezone-error/33024/1

https://ask.libreoffice.org/t/dates-in-database-column-have-shifted-by-one-day/41801

https://ask.libreoffice.org/t/date-is-set-one-day-before-the-entered-date-for-dates-with-month-between-04-and-10/48221

My Linux box is running Zorin OS 16.3 and LibreOffice 7.5.5.2 (x86_64), although I have used various iterations of Linux and LibreOffice with this database over the years and on several different machines, and I never had any date issues before I edited the data on a Windows machine.

My Windows machine is running Windows 11 Enterprise, version 22H2 and LibreOffice 7.5.2.2 (x86_64).

I have tried turning off “Use ODBC conformant date/time literals,” but that had no impact on the issue.

Here is my database file:
10111 (dummy data only - from Linux and Windows).odb (194.1 KB)

Any ideas on what might be causing this and how to fix it? Thanks!!

[RobertG]

Might be a different Java-version on Windows an Linux. Would be interesting to see if the buggy behavior also exists with Firebird.

For using Firebird to test this: Set Tools → Options → LibreOffice → Advanced → Enable experimental Features on only for creating a Firebird database (or for migrating a database from HSQLDB to Firebird - please use a copy from HSQLDB-database, not the only original!). Set Enable experimental Features off after you have created the database.

[iplaw67]

Some ideas:

• check the “default” date setup for each version in Calc preferences - 01.01.1899, 1900, or 1904?

• check your daylight saving time setup for each machine (this rings a bell for me as a bug in LO bugzilla) ;

• check that the locale settings are equivalent between the Linux and Windows machines.

[Wanderer]

@iplaw67

daylight saving time

That’s really a good idea. I had no clue, why a difference would be limited to a part of the year…
.
So a wild guess could be a bug where DST is compensated not by -1 hour, but by -1 day… Possible, if one assumes a field in hours, while it actually has Calcs “days since …” with hours/time as fraction of the day.

[Villeroy]

The data type of column “Date” in table “Documents” is DATE. There is no time stored in that column. When you format the column as timestamp the time is 00:00:00.

[iplaw67]

Timezone settings at the OS level have been reported as having an effect on date display and storage, see e.g.

LibreOffice Base displays MySQL date fields off by one day

I have Base connected to my localhost MySQL server with a regular table with a date field in it. I open the table in base and enter a date into the date field, and after it updates it displays the date minus one day. When I view the database through MySQL Workbench, the dates are recorded correctly, despite continuing to come up as one day earlier when viewed through Base. I fixed it Incidentally, earlier I had trouble connecting to my database due to a timezone error. For some reason it ha…

and

Date is set one day before the entered date for dates with month between 04 and 10

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…

There is also used to be a bug report in LO BZ about this when using hsqldb embedded or some Java drivers to connect to other DB server instances. I’d need to trawl through the reports again to find it.

[iplaw67]

For example:
https://bugs.documentfoundation.org/show_bug.cgi?id=102945

[forliberty]

Thank you for all the responses, everyone. It turns out that somehow the time zones create a problem for the HSQLDB embedded database, but not the Firebird embedded database.

The time zones for the Linux and Windows machines were different. While the times on both machines was the same, my Linux box is set to PST (Los Angeles, US), while my Windows box was set to UTC-12:00 (Int’l Date Line West). Both machines were set to recognize daylight savings time automatically.

I tested this by re-creating a brand new embedded HSQLDB database on the Linux machine with nothing more than a Dates field and a text field, inputting the same data into both. I then opened the database on my Windows machine. Of course, the dates from April through October were again off by a day. I then changed the time zone on the Windows machine to match my Linux machine. Lo and behold, all the dates were correct.

I then did the same test with an embedded Firebird database. There was no longer any issue with the dates - regardless of the time zones.

Thank you again for helping me figure out why this was happening. Kudos to both iplaw67 (for identifying the timezone issue) and RobertG (for suggesting I try the Firebird database). I wish I could pick two solutions, but it seems the platform only accepts one.

I have two new questions as to where I should go from here. Sticking with HSQLDB is not an option for me. My users are in different time zones, and I’m not going to make them change their computer clocks so they can enter data. (I can’t even permanently change the clock on my own Windows box - which is really a virtual machine the company provides and over which I have little control.) More importantly, I no longer have any confidence in the HSQLDB embedded database.

My first question is whether I should stick with LibreOffice Base at all. My confidence in the software has been rather shaken, as this is quite a significant bug that seems to have been around for a while with no action taken to resolve it. Should I just switch to Access, or can I trust the software if I move to a different database backend?

If I do decide to stick with LibreOffice Base, what database backend should I use, and do you have any tips on migrating my database? My database is complex, with many linked tables, queries, reports, and a form. The data it contains took many months of user time to enter. In looking at the process for converting from HSQLDB to Firebird (https://wiki.documentfoundation.org/Documentation/HowTo/MigrateFromHSQLDB), it seems like a mess. I’m just a user, not a database whiz.

[Villeroy]

@forliberty

If I do decide to stick with LibreOffice Base, what database backend should I use

I recommend HSQL2 instead of the embedded HSQL 1.8 of 2005.

• Save a copy of your database document in a separate directory
• Add a subdirectory “driver” to the directory where you stored the document
• Download Download hsqldb-2.4.1.zip (HyperSQL Database Engine (HSQLDB))
• Extract lib/hsqldb.jar to the driver directory
• Download and install my Python macro from Apache OpenOffice Community Forum - [Python] Macro to extract and reconnect embedded HSQLDB - (View topic) which makes it easy to convert from 1.8 to 2.4.1.
• Open your database document and run Tools>Macros>Run … pyDBA>ExtractHSQL>Main which will extract the embedded database files to a subdirectory “database” and connect the document with the extracted version using the given driver/hsqldb.jar.
• Test your tables, queries, forms and reports.
• Save the modified database document.

Further upgrades (recent version is 2.7) may work like this:

• Make a backup copy of the database folder
• Rename the old hsqldb.jar in the driver directory to hsqldb-2.4.1.jar
• Download a recent version of HSQL and extract the hsqldb.jar to the driver subdirectory.
• Open your document and run the same macro again. When prompted, decide to reconnect with the existing external database.

I run HSQL 2.4.1 since many years. This version fits all my needs. If you feel courageous, you may run the extracted database in server mode for simultaneous multi-user access, but that’s a different story.

[RobertG]

@Villeroy : Did you test the behavior of HSQL2 in different time zones? Isn’t it the same problem as it is with internal HSQLDB and JDBC-connected databases?

I would try to switch the database to Firebird instead.

@forliberty : If you have problem with migrating from HSQLDB to Firebird: Write a private email. Problems will appear in views and queries if the SQL-code contains functions, which aren’t known (with the same name) in Firebird.

[forliberty]

Thanks, Villeroy and RobertG! I tried both of your suggestions.

First, I tried Villeroy’s suggestion (following his instructions) of moving to an extracted HSQL2 database. Everything seemed to work fine (although I did not get to the stage of testing the date issue), but my issue with the extracted database is that I would have to make changes to my users’ computer setups, e.g., downloading the database drivers, creating special folders, etc. Having walked through that on my own computer, it’s just too messy for my use case. I don’t want to become tech support for my users.

Then, I tried RobertG’s suggestion of switching my database from embedded HSQLDB to embedded Firebird. I followed the instructions here: Migrate from HSQLDB - The Document Foundation Wiki. Although it seemed daunting (especially since I use sub-forms), it actually wasn’t too bad of a process, and everything seems to be working fine. I tested the date issue by working on my Windows box for a while, and had no problems whatsoever. I’ve also noticed that searches of the database are much, much faster. (Embedded HSQLDB was a real dog.) So thank you again to everyone for all your help!!

[Villeroy]

@forliberty

but my issue with the extracted database is that I would have to make changes to my users’ computer setups, e.g., downloading the database drivers, creating special folders, etc.

The external db will be faster and much more robust. Sooner or later (sooner with growing volume) the embedded database will crash with a total loss of data.

Distribute a zip with all the files and a macro which does the setup for you.
Apache OpenOffice Community Forum - FreeHSQLDB v.0.4 - (View topic) includes

• a dialog to specify any driver and any location of the database files.
• an auto-install module which adjusts the relative paths of database and driver. Just tell the users to open the database document with the auto-installer whenever the database has been moved.
• a macro to extract all forms out of the Base container which gives a lot more advantages (e.g. open form without Base document, import other forms by simple hyperlinks).

Demo with external database, driver and auto-install macro: FilterData-HSQL-2.4

IMHO, embedded Firebird is not production ready. It has the same stability issues because it is embedded and it is not as well adjusted to Base as HSQL is.

[forliberty]

Thank you for this input, Villeroy. I have seen warnings about embedded database corruption on this site, but I have not seen any issues (aside from the date issue described in this post) over the past four years. I do back up the database daily as a precaution, however. In your experience, how big does the embedded database have to get before it starts experiencing corruption? Mine is about 2MB right now.

Thanks again.

[Villeroy]

One scenario is that you close the database session, close the laptop lid, hibernate or shut down too quickly while the database being wrapped back into the Base document.
I successfully used embedded HSQL for several months with a database that grew up to 7 MB. I closed the entire office suite and waited some seconds before I finished the the OS session. And yes, I did daily backups too. Never had any trouble with that database.