Ask Your Question

LibreOffice Base displays MySQL date fields off by one day

asked 2018-08-28 06:45:13 +0200

paradigm gravatar image

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: 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

edit retag flag offensive close merge delete


Apparently correcting the time zone also fixed my relationships model. Previously my relationships model showed the tables where I placed them but didn't display the actual relationship lines. However MySQL Workbench showed duplicate foreign keys in my tables because I had tried to save my relationships several times, and apparently they did save, but just wouldn't display. Now with the server timezone corrected my relationships display as well.

paradigm gravatar imageparadigm ( 2018-08-28 07:07:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-08-28 17:50:29 +0200

Ratslinger gravatar image


Have no way to test this but it may the method you used for timezone correction. Please see this link -> Base - Connect MySQL JDBC TimeZone error for another possibility.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-28 06:45:13 +0200

Seen: 250 times

Last updated: Aug 28 '18