Why does jdbc with sqlite store data with local timezone?

Development Misc
1

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?

2

https://www.sqlite.org/lang_datefunc.html

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.

3

There is a long discussion at

4

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:

image
image1403×855 77.8 KB

One hour has no data and the next double values.

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

1 Like
5

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!