Why does jdbc with sqlite store data with local timezone?

[chaoshome]

The time in the sqlite database is in local time instead of UTC, which leads to inconsistent data when time changes from CEST to CET.
E.g. this value is stored every minute:

 sqlite> SELECT * from aktuelleleistung_strom_0127 where (time > datetime('2024-10-27 02:00:00')) and (time < datetime('2024-10-27 02:05:00'));
┌─────────────────────────┬──────────────────┐
│          time           │      value       │
├─────────────────────────┼──────────────────┤
│ 2024-10-27 02:00:00.867 │ 47.85            │
│ 2024-10-27 02:00:00.875 │ 100.316666666667 │
│ 2024-10-27 02:01:00.868 │ 50.7833333333333 │
│ 2024-10-27 02:01:00.875 │ 100.433333333333 │
│ 2024-10-27 02:02:00.869 │ 48.45            │
│ 2024-10-27 02:02:00.875 │ 100.866666666667 │
│ 2024-10-27 02:03:00.867 │ 99.25            │
│ 2024-10-27 02:03:00.868 │ 47.9833333333333 │
│ 2024-10-27 02:04:00.871 │ 47.7             │
│ 2024-10-27 02:04:00.888 │ 100.066666666667 │
└─────────────────────────┴──────────────────┘

Is there a reason why UTC is not used?

[jdandrews (Jerry Andrews)]

Date And Time Functions

I would guess that, since SQLite doesn’t have a dedicated date time type, you need to manage the time zone directly, as indicated in the link above. If you use functions that return UTC by default, you will get the behavior that I think you want.

[Wolfgang_S]

There is a long discussion at
https://github.com/openhab/openhab-addons/issues/13775

[chaoshome]

@jdandrews

Date And Time Functions

I would guess that, since SQLite doesn’t have a dedicated date time type, you need to manage the time zone directly, as indicated in the link above. If you use functions that return UTC by default, you will get the behavior that I think you want.

I’m not accessing sqlite directly, it is all through openhab. The data is stored by persistence and the timezone information is then already lost. And if I retrieve it in a plot it looks like this:

One hour has no data and the next double values.

@Wolfgang_S

There is a long discussion at
[jdbc] Charts don't show the correct dates/times because persistence doesn't check with the right timezeone · Issue #13775 · openhab/openhab-addons · GitHub

Thanks for the hint. jdbc seems to have several timezone issues.

[jdandrews (Jerry Andrews)]

I love that chart. It took me a minute to parse what was going on… thanks for posting it.

I see from the discussion under the link posted by @Wolfgang_S that there’s more going on here than I understood. Many developers are tripped up by the differences between different database’s handling of date/time data—indeed, older Java date/time constructs are also confusing. I see I have a lot to learn before I have much useful to contribute!