How to solve warning JDBC::store: Unable to store item

[milo (M)]

Hello,

I getting currently this strange warning in my logs…

JDBC::store: Unable to store item
Error: 1292-22007: Incorrect datetime value: '1970-01-01 01:00:00' for column `openhabian`.`item0207`.`value` at row 1

If i check the item via rest i getting this value back

{
  "link": "[url]/rest/items/dwdweatherwarningstuttgart_effective2",
  "state": "1970-01-01T00:00:00.000+0000",
  "stateDescription": {
    "pattern": "%1$tT, %1$td.%1$tm.%1$tY",
    "readOnly": false,
    "options": []
  },
  "metadata": {
    "semantics": {
      "value": "Point_Status",
      "config": {
        "isPointOf": "group_weatherstation"
      }
    }
  },
  "editable": false,
  "type": "DateTime",
  "name": "dwdweatherwarningstuttgart_effective2",
  "label": "Issued at",
  "category": "dwd",
  "tags": [
    "Status"
  ],
  "groupNames": [
    "group_weatherstation"
  ]
}

[milo (M)]

Is there any help for this one?

[splatch (Łukasz Dywicki)]

If you can, please inspect database and type of value column. Sometimes when item type is redefined schema retains old type causing such errors.

[milo (M)]

See attached the definition

…

No value stored so far

[splatch (Łukasz Dywicki)]

It looks good, you have datetime item and timestamp type for value column, which is valid for temporal data. However it does fail to store date time value. Can you share which database and which OH version you use? From error code I presume MySQL. If that’s true value you have might not fit timestamp type, because mysql will prefer numeric representation (epoch/unix timestamp). You either have to convert it (through modification of JDBC service config) or change database column type to one which will accept above string. Here is some note from stack overflow: sql timestamp - MySQL 1292 Incorrect datetime value - Stack Overflow

[milo (M)]

Hello,

this is how i update the item…

items.getItem("dwdweatherwarning_expires3").postUpdate(0);

is this wrong?

[splatch (Łukasz Dywicki)]

It is ok, however this 0 is being then translated into DateTime and then, it needs to be translated into database value. Issue you observe in logs is caused by last stage - value which is generated by insert into statement is not accepted by database engine. So you need to tune that part.
I am not quite sure how to do it properly as I did not need to customize SQL statements in jdbc config since … ever?

[milo (M)]

Found this bug report on GitHub

[JDBC] MySQL OH DateTime items sets DB value field to TimeStamp instead of DateTime