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 had a problem with my EDT time zone. So I fixed it by adding the line “default_time_zone=’+04:00’” to my mysqld.cnf. While I was writing this question I realized that my timezone is actually -4:00. I changed it and that solved the issue.
Base now shows the same time as other MySQL viewers, for now at least. However When daylight savings time ends, it’s probably going to display the wrong date when viewed between 11:00pm and midnight unless I edit the timezone to -5:00, which I probably wont remember to do.
So the question remains, why should time timezone of my server effect the date data read from a field? Maybe for timestamps I could understand but for past dates that I want to record, it seems like the timezone shouldn’t have any effect on how a past date is displayed. Especially when it’s recorded correctly in the table. I mean, a person’s birthday should be read the same no matter what timezone their server is in.
To be fair, I don’t know if this is a Base issue or a JDBC issue. For testing purposes I guess I could set the timezone back to +4:00 and connect using ODBC and see if it has the same issue. But I’ll have to save that for later, I’ve have quite enough for now.
Specs:
LibreOffice Version: 6.1.0.3
Build ID: efb621ed25068d70781dc026f7e9c5187a4decd1
CPU threads: 6; OS: Linux 4.15; UI render: default; VCL: gtk2;
Locale: en-US (en_US.UTF-8); Calc: CL
Ubuntu version 18.04.1 LTS (Bionic Beaver)
mysql Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using EditLine wrapper
Using JDBC mysql-connector-java-8.0.12.jar