[SOLVED] Time in mysql incorrect

Very much info on details of the problem I am having below. This is my very first post though I have been reading the community entries for quite some time now. If I have too much info, or asking too many questions (there is really only one, Why is my time in mysql tables incorrect) I apologize in advance.

  • Platform information:
    • Hardware: CPUArchitecture/RAM/storage : Raspberry Pi Model B Rev 2
    • OS: what OS is used and which version: Raspbian GNU/Linux 9 (stretch)
    • Java Runtime Environment: which java platform is used and what version:
      openjdk version “1.8.0_212”

OpenJDK Runtime Environment (Zulu8.38.0.163-CA-linux_aarch32hf) (build 1.8.0_212-b163)

OpenJDK Client VM (Zulu8.38.0.163-CA-linux_aarch32hf) (build 25.212-b163, mixed mode, Evaluation)

  • openHAB version: openHAB 2.4.0-1 (Release Build)
  • Issue of the topic: please be detailed explaining your issue - see below.
  • Please post configurations (if applicable):
    • Items configuration related to the issue
    • Sitemap configuration related to the issue
    • Rules code related to the issue
    • Services configuration related to the issue
  • If logs where generated please post these here using code fences:
  • I have several One-wire Temperature sensors connected to my Pi with Openhab2 via the OneWireGPIO.
  • Persistence is mysql.
    - it is running fine except that the time in the mysql tables is one hour earlier than my local time.
  • I am in Eastern (Daylight) time.
  • Time on Pi is correct - see below.
  • Time on mysql is correct - see below.
  • Time in table is one hour earlier : see below.

Summary of various time related commands from the system and mysql:

Various time captures from MariaDB…
NOTE – I have manually inserted “---------” before each entry to make
it easier to differentiate the various entries.

---------MariaDB [OpenHAB]> select @@global.time_zone;
±-------------------+
| @@global.time_zone |
±-------------------+
| SYSTEM |
±-------------------+
1 row in set (0.00 sec)

---------MariaDB [OpenHAB]> select curdate();
±-----------+
| curdate() |
±-----------+
| 2019-10-10 |
±-----------+
1 row in set (0.00 sec)

---------MariaDB [OpenHAB]> select now();
±--------------------+
| now() |
±--------------------+
| 2019-10-10 16:52:50 |
±--------------------+
1 row in set (0.00 sec)

---------MariaDB [OpenHAB]> select unix_timestamp();
±-----------------+
| unix_timestamp() |
±-----------------+
| 1570740772 |
±-----------------+
1 row in set (0.01 sec)

---------MariaDB [OpenHAB]> select utc_time;
±---------+
| utc_time |
±---------+
| 20:52:54 |
±---------+
1 row in set (0.01 sec)
NOTE: Calculating the timestamp from the above UTC time equates
to 1570740774, or two seconds after the above unix timestamp
– the amount of time for me to enter the next command and press
return.

---------MariaDB [OpenHAB]> select sysdate();
±--------------------+
| sysdate() |
±--------------------+
| 2019-10-10 16:52:55 |
±--------------------+

1 row in set (0.01 sec)

NOTE: There is a +4 hour offset from Eastern Daylight Time to UTC
so this time shows the correct local time.

---------The Pi’s system time corresponds to the MariaDB’s time
[16:49:56] openhabian@VincesopenHABianPi:~$ date
Thu Oct 10 16:52:58 EDT 2019

---------Entry in OpenHAB log showing time 17:01:19.623 with temp 102.0866…
2019-10-10 17:01:19.623 [vent.ItemStateChangedEvent] - Temperature_Sensor_2 changed from 102.5366 °F to 102.0866 °F

---------Time from a table in mysql shows 16:01:19 which corresponds to entry in log…This time
appears to be 1 hour ealier or essentially Eastern STANDARD Time, not Daylight time.
| 2019-10-10 16:01:19 | 102.0866 |
±--------------------±---------+
3369 rows in set (0.34 sec)

MariaDB [OpenHAB]> select version();
±-------------------------+
| version() |
±-------------------------+
| 10.1.38-MariaDB-0+deb9u1 |
±-------------------------+
1 row in set (0.00 sec)

openHAB 2.4.0-1 (Release Build)

in mysql.cfg there is the line
url=jdbc:mysql://localhost:3306/OpenHAB?serverTimezone=EST

which may be causing the problem.

If i change it to:
url=jdbc:mysql://localhost:3306/
I get the following error and warning in the log:

2019-10-14 17:18:24.736 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/, user=openhab

java.sql.SQLException: The server time zone value ‘EDT’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

If I change it to:
url=jdbc:mysql://localhost:3306/OpenHAB?serverTimezone=EDT

2019-10-14 17:24:33.948 [ERROR] [sql.internal.MysqlPersistenceService] - mySQL: Failed connecting to the SQL database using: driverClass=com.mysql.jdbc.Driver, url=jdbc:mysql://localhost:3306/OpenHAB?serverTimezone=EDT, user=openhab

I loaded time zone data using into mysql using:
sudo mysql_tzinfo_to_sql … and get everything EXCEPT EDT data!
I receive warning Unable to load ‘/usr/share/zoneinfo//EDT’ as time zone - skipping it.

Note the double // (forward slashes) just before EDT - they seem strange.

Attempt to update zone info data but it tells me its up to date.

Any thoughts?

java.sql.SQLException: No timezone mapping entry for ‘EDT’

Please use code fences
Although I beleive this is a MariaDB problem and not openHAB

Thank you for your reply Vincent. I appreciate it.

Although I have not solved the problem yet, I will close this, regroup, check out the mysql sites, and if i need to repost here, I will use code fences as you suggest.

Have a great day.