How to fix Incorrect datetime value '1970-01-01 01:00:00' error with MySQL TIMESTAMP in OpenHAB JDBC Persistence?

Setup, Configuration and Use Beginners
1

Hi everyone,

I’m using OpenHAB with MySQL (MariaDB) via the JDBC persistence service. I have an item linked to a MySQL table where the value column is of type TIMESTAMP(3).

When OpenHAB tries to insert or update the item with the datetime value '1970-01-01 01:00:00', I get the following error:

Error: 1292-22007: Incorrect datetime value: '1970-01-01 01:00:00' for column `openHab`.`item0066`.`value` at row 1 

I checked my MySQL SQL mode and it does not include NO_ZERO_DATE. The value column is defined as:
Field Type Null Key Default Extra
value timestamp(3) YES NULL

item definition is

DateTime Time_Outdoor_DWDWeatherWarningStuttgartEffective2 “Issued at [%1$tT, %1$td.%1$tm.%1$tY]” (Group_House_WeatherStation, Group_House_NotPersistedEveryChange )[“Status”]

I suspect this has something to do with the minimum allowed timestamp in MySQL and timezone conversions.

My questions are:

  • What is the best way to solve this error when using OpenHAB with MySQL JDBC persistence?

  • Should I switch the value column type to DATETIME(3) instead of TIMESTAMP(3) to avoid timezone-related issues?

  • Are there any recommended configurations or workarounds on the OpenHAB or MySQL side to handle dates around the UNIX epoch?

Any insights or experience with this problem would be greatly appreciated!

Thanks in advance!

2

I remember that I had something similar years ago. I setup both openHAB and MySQL/mariadb to have the same time zone. I’m pretty sure I added it to the jdbc connection string.

My example:

jdbc:mariadb://10.0.2.10:3306/openhab?serverTimezone=Europe/Amsterdam

3

I have this already….

url=jdbc:mariadb://localhost:3306/openHab?serverTimezone=Europe/Berlin

in the databsae I see

@@global.time_zone @@session.time_zone
SYSTEM SYSTEM
4

Currently not able to check, but I think Atleast the session should be Europe/Berlin in this case.

@laursen knows a lot about this. Maybe he has some advice

5

I checked it via phpmyadmin so I guess the session is than okay because it’s not the openhab session

6

@laursen can you help here?

How to reset a DateTime item?
7

This is not a trivial issue.

@milo - I suspect that your DateTime Item is used for containing time only, e.g. 01:00:00, is that correct? Which time zone are you in?

Unfortunately we don’t have a TimeType for Time Items, so currently this is “hacked into” DateTimeType. So I think what might happen is:

  • A DateTimeType object is created from the string “01:00:00” . This will be parsed as 1970-01-01 01:00:00 using the system time zone.
  • Internally this is stored as UTC. This is just an implementation detail for describing the full picture.
  • The Item state is then provided to MySQL Persistence, which will extract getInstant().toEpochMilli() and create a java.sql.Timestamp value from this long: “time milliseconds since January 1, 1970, 00:00:00 GMT. A negative number is the number of milliseconds before January 1, 1970, 00:00:00 GMT.”
  • In MySQL this is mapped to DATETIME(3).
  • This type supports a range going back to year 1, see https://dev.mysql.com/doc/refman/8.4/en/datetime.html. By contrast, the range for TIMESTAMP starts at '1970-01-01 00:00:01' UTC.
  • The type was changed in openHAB 4.2, see [jdbc] Provide better MySQL datatype for `DateTimeType` by jlaur · Pull Request #16604 · openhab/openhab-addons · GitHub
  • Existing tables created before 4.2 were not automatically changed, but the console command jdbc schema check should be able to show these issues, and jdbc schema fix should even be able to fix them.

So yes, you should switch to DATETIME(3).

Out of curiosity: What is your use-case for persisting time-only values?