JDBC Mysql - different timezone on OH2 and MySQL Servers

Add-ons Persistence Services
1

Hey there,

I have a MySQL server running on a machine that (for reasons outside my control) is running in the America/Los_Angeles timezone while the OH2 Server is running in TZ Europe/Madrid.
I previously used to use the mysql persistence service with the configuration option to use OH2’s local time and all was happy.
After moving to the JDBC MySQL service for stability reasons, I seem to be unable to get the same behaviour.
At the end of the connection string I’ve added ?serverTimezone=America/Los_Angeles but all my logging shows up 9 hours back in time anyway. I also tried ?serverTimezone=Europe/Madrid instead and had the same result.
How can I make OH2 use the right TZ offset when logging its things so my charts aren’t all messed up?

2

Just a guess: Try

enableLogTime=true

in jdbc.cfg

3

Ah, I forgot to mention I tried that :slight_smile:
It just dumps lots of information in the logs (I watched it in the karaf console) about how long the interactions with the database take, so it’s something entirely different

4

:frowning:

5

sorry, I don’t know how you could replace localtime=true with JDBC… but:

on a side note, what stability reasons did you encounter?

6

That was in this thread here: Habpanel gets completely broken by n3-line-chart from MySQL but no errors in log
Basically charts with lots of datapoints throw the MySQL persistence service into a stuck state.

1 Like
7

Has anyone come up with an answer to the original issue? That is, persisting to a mysql with a different timezone? What I am seeing is that the data is being stored in the timezone of the database server and being queried in the timezone of openhab. The effect is that habpanel never shows the most recent data.

Thanks!

8

I’m having the same problem here. Did you solved it?

9

Negative. I ultimately worked around it using a server where i was able to control the time zone

10

Oh the irony. My workaround just fell apart :laughing:
But I found the solution now: Getting JDBC MySQL persistence back to work with 2.5 Snapshots on Windows
The trick is just adding the ?serverTimezone=America/Los_Angeles (in my case) to the end of the JDBC URL in the mysql.cfg
For a list of valid timezones see https://en.wikipedia.org/wiki/List_of_tz_database_time_zones (stick to the canonical ones)